Trying to figure out how to select multiple number rows

    I am trying to figured out how to select rows automatically from an active cell(A1), blank (A2), blank(A3) blank(Etc). There will be data in column B-?. Colum A will have one item number, and specifications in colum B-?, and the rows after until a new item number is given.


    I have (for example):


    A B C
    1 234 abc abc
    2 abc abc
    3 abc abc
    4 abc abc
    5 abc abc
    6 abc abc
    7 234 abc abc
    8 abc abc
    9 abc abc
    10 234 abc abc
    11 abc abc
    12 abc abc
    13 abc abc
    14 abc abc




    I need to figure out the VB when A1 is selected, Rows 1-6 are selected.
    Thank for your help. I have learned a lot from here, but did not see this type off selection set when I searched.


    For clarification, and example has been posted.

    The following Event macro works with your example. It should be placed in the sheet module that has the data. [wsc] * [/wsc]
    [vba]Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim r As Range
    If Target.Column = 1 And Target.Cells.Count = 1 Then
    If Not IsEmpty(Target) Then
    Set r = Target.End(xlDown).Offset(-1, 3)
    If r.Row = 65535 Then Set r = r.End(xlUp)
    Application.EnableEvents = False
    Range(Target, r).Select
    Application.EnableEvents = True
    End If
    End If
    End Sub[/vba]

    Ok, I have added the Private sub, how to I make it work with the following code in my module?


    itemNo = Application.InputBox("Enter Item Number: ", 2)

    Cells.Find(What:=itemNo, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate


    'select specs rows operation here


    Selection.Copy
    Range("A60").Select
    ActiveSheet.Paste

    Oh. I thought you were selecting the cell yourself. Pitch the earlier code and try the following in your macro (in the general module)
    [vba]
    Dim r As Range, itemNo As Variant
    itemNo = Application.InputBox("Enter Item Number: ", 2)
    If itemNo = "" Then Exit Sub


    Cells.Find(What:=itemNo, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
    SearchFormat:=False).Select



    Set r = Selection.End(xlDown).Offset(-1, 3)
    If r.Row = 65535 Then Set r = r.End(xlUp)
    Range(Selection, r).Copy Range("A60")
    [/vba]

    Thats it! Thank you very much!


    Thats Exactly what I needed. Awesome. That was that last step I needed to figure out, and you made it so simple. Plus I learned a new copy method with out the clipboard. Thanks.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login