How to select a range of rows using two variables in VBA

Question

I'm brand new to VBA and am trying to write a macro to pull specific data from all workbooks in the same directory into a master workbook. I'm getting stuck when trying to select a range of data using variables so that I can copy and paste the data into the master workbook. I've been watching videos and reading through forums, but can't seem to get the macro to work.

I have an Excel sheet that lists employees in column A, with all the data I want to copy about the employees in columns B, C, D, E, and F (in subsequent rows). So for example, row 1 contains the first employee in cell A1, and then rows 2 through 5 contains the data in columns B through F. Row 6 contains the next employee's name in cell A6, and the data about them resides in rows 7 through 9 (columns B-F). I want to copy rows 2-5 and paste them into the master workbook, and then copy 7-9 and paste into master, 8-14, and so on and so forth.

My first attempt was to define two variables as integers. Then I tried to find the name of the first employee in column A and select the row after, and set the first variable equal to that row. Then find the name of the second employee, select the row before and set variable 2 equal to that row. Then select the range using those two variables. Here's what my code looks like:

Sub SelectConsultantData()
Dim Consultant1 As Integer, Consultant2 As Integer
Dim ConsultantRange As Range

    Columns("A:A").Select
    Selection.Find(What:="Andrew", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Consultant1 = Rows(ActiveCell.Row).Select
    Consultant1 = Consultant1 + 1
    Columns("A:A").Select
    Selection.Find(What:="Bob", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Consultant2 = Rows(ActiveCell.Row).Select
    Consultant2 = Consultant2 - 1
    Set ConsultantRange = Range(Consultant1, Consultant2).Select

End Sub

Any idea what I'm doing wrong, or can anyone think of a better approach? Also please let me know if I need to provide further context.

Thanks in advance for any help.

First thing your Consultant1 & 2 variables need to be declared as Range. Second does the structure remain the same throughout...i.e. the name in A1 followed by the information in B2:F5? — Marshall, May 02 '13 at 19:55
Yes the structure remains the same throughout. The only thing that changes is the number of rows under each employee. So the first employee name is in A1, information for that employee is in B2:F5 (4 rows), employee 2 name is in A6, information in B7:F9 (2 rows), employee 3 name is in A10, information in B11:F14 (3 rows), etc. Some employees have only one or two rows of data, some have 40-50 under their names. — Anthony, May 02 '13 at 20:25
Once you have identified and selected the range for the first employee, what's the next step? Is it to copy it to a new worksheet in your master workbook? in the same format? new format? — Eddie, May 03 '13 at 08:57

score 0 · Accepted Answer · edited May 23 '17 at 12:15

Your code can be re-written as below. Avoid using Select in your code. Check this link to know why.

Sub SelectConsultantData()
    Dim Consultant1 As Integer, Consultant2 As Integer
    Dim ConsultantRange As Range

    Dim rngFind As Range
    Set rngFind = Columns("A:A").Find(What:="Andrew", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)

    If Not rngFind Is Nothing Then
        Consultant1 = rngFind.Row + 1
    End If

    Set rngFind = Columns("A:A").Find(What:="Bob", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)

    If Not rngFind Is Nothing Then
        Consultant2 = rngFind.Row - 1
    End If

    If Consultant1 > 0 And Consultant2 > 0 Then
        Set ConsultantRange = Range(Cells(Consultant1, 2), Cells(Consultant2, 6))
    End If

End Sub

How to select a range of rows using two variables in VBA

1 Answers1

Linked