How to select a range of rows using two variables

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.

Answer 1

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