如何在 VBA 中使用两个变量选择一系列行

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

提问by Anthony

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.

我是 VBA 的新手，正在尝试编写一个宏，将同一目录中所有工作簿中的特定数据提取到主工作簿中。我在尝试使用变量选择一系列数据时遇到了困难，以便我可以将数据复制并粘贴到主工作簿中。我一直在观看视频和阅读论坛，但似乎无法让宏正常工作。

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.

我有一个 Excel 工作表，其中列出了 A 列中的员工，以及我想要复制的有关 B、C、D、E 和 F 列（在后续行中）中员工的所有数据。例如，第 1 行包含单元格 A1 中的第一个雇员，然后第 2 行到第 5 行包含 B 到 F 列中的数据。第 6 行包含单元格 A6 中的下一个雇员的姓名，有关他们的数据位于第 7 行到第9（BF 列）。我想复制第 2-5 行并将它们粘贴到主工作簿中，然后复制 7-9 并粘贴到主工作簿、第 8-14 行，依此类推。

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:

我的第一次尝试是将两个变量定义为整数。然后我尝试在 A 列中查找第一个员工的姓名并选择其后的行，并将第一个变量设置为等于该行。然后找到第二个员工的姓名，选择前一行并将变量 2 设置为等于该行。然后使用这两个变量选择范围。这是我的代码的样子：

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.

在此先感谢您的帮助。

采纳答案by Santosh

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

您的代码可以重写如下。避免在代码中使用 Select。检查此链接以了解原因。

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