希望在 excel 中选择不确定数量的行作为较大 VBA 宏的一部分

Looking to select an undetermined number of rows in excel as part of larger VBA macro

提问by heavyarms

I'm working with an excel book containing a large number of sheets; the first sheet is linked to an external program and pulls in data via an external function, and the number of lines imported varies significantly.

我正在处理一本包含大量工作表的 excel 书；第一张表链接到外部程序并通过外部函数拉入数据，导入的行数差异很大。

This block data is the disseminated over a number of subsequent sheets. The first step has been to populate column A (row name) with the number of rows in sheet 1. From here the data is split over a number of columns (currently B->L). The top row uses an IF() function to populate the first row, and I'm looking to write a clean macro to copy this formula to row x (which varies with each data import refresh) and then paste values for a manageable file size.

该块数据通过多个后续工作表传播。第一步是用工作表 1 中的行数填充 A 列（行名称）。从这里开始，数据被拆分为多个列（当前为 B->L）。顶行使用 IF() 函数填充第一行，我希望编写一个干净的宏来将此公式复制到行 x（随着每次数据导入刷新而变化），然后粘贴值以获得可管理的文件大小.

Here's what I've got so far; it works, but it's fairly (read: VERY!)clumsy:

这是我到目前为止所得到的；它有效，但它相当（阅读：非常！）笨拙：

Sub Refresh_Data()  
    Sheets("Sheet2").Select  
    ActiveWindow.ScrollWorkbookTabs Sheets:=13  
    Sheets(Array("Sheet2" ... "Sheet25")).Select     
    Sheets("Sheet2").Activate  
    Sheets("Sheet25").Select Replace:=False  
    Range("B1:L1").Select  
    Selection.Copy  
    Range("__B2:B1000__").Select  
    ActiveSheet.Paste  
    Application.Calculate  
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst  
    Sheets(Array("Sheet2" ... "Sheet25")).Select  
    Sheets("Sheet2").Activate  
    Sheets("Sheet25").Select Replace:=False  
    Sheets("Sheet2").Select  
    Range("B3").Select  
    Sheets(Array("Sheet2" ... "Sheet25")).Select  
    Sheets("Sheet2").Activate  
    Sheets("Sheet25").Select Replace:=False  
    Range("B3:L4").Select  
    Range("__B2:L1000__").Select  
    Application.CutCopyMode = False  
    Selection.Copy  
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _  
        :=False, Transpose:=False  
    Sheets("Check_sheet").Select  
    MsgBox "Update complete"  
End Sub`

The main thing I'm looking to achieve is to replace the code B2:L1000with something that can assess the number of rows in column A and select a range in rows B to L accordingly.

我希望实现的主要目标是B2:L1000用可以评估 A 列中的行数并相应地选择 B 到 L 行中的范围的代码替换代码。

Since column L is the last populated column, I don't see why this can't also be done horizontally rather than defining "B:L" incase future columns need to be added.

由于 L 列是最后一个填充的列，我不明白为什么这也不能水平完成，而不是定义“B:L”，以防需要添加未来的列。

回答by Tony Dallimore

Although the earlier answer has merits:

虽然较早的答案有优点：

1) I would not use COUNTA because if there are empty cells in the row or column, the cells at the bottom or the right will be ignored.

1）我不会使用 COUNTA 因为如果行或列中有空单元格，底部或右侧的单元格将被忽略。

2) I would never rely on the user picking the correct sheet to be used before running a macro; particularly one with so many sheets.

2）我永远不会依赖用户在运行宏之前选择要使用的正确工作表；特别是一张有这么多床单的。

My reaction to the question is that you have set Macro Record, wandered around your workbook and then stopped the record. You select one thing, then another. You scroll through the sheets. To me most of the statements are not clumsy they are pointless.

我对这个问题的反应是你设置了宏记录，在你的工作簿中徘徊，然后停止了记录。您选择一件事，然后选择另一件事。您滚动工作表。对我来说，大多数陈述并不笨拙，它们毫无意义。

The following does include an answer to your question about finding the last row of column A but it is more a tutorial about finding the dimensions of a range, getting data out of the range and then putting it somewhere else. This seems to be most of what you are trying to do with the most minimal understanding of VBA. I am sorry if this criticism is unfair but that is the impression your question gives to me.

以下内容确实包含了有关查找 A 列最后一行的问题的答案，但它更像是关于查找范围维度、获取范围外的数据然后将其放在其他地方的教程。这似乎是您对 VBA 最起码的理解尝试做的大部分事情。如果这种批评不公平，我很抱歉，但这就是你的问题给我的印象。

Sub Test()

  Dim RowS01Max As Integer
  Dim Sheet1Data() As Variant

  ' With Sheets("Sheet1") allows you to access data within worksheet Sheet1
  ' without selecting it.
  ' Range("A1:C11") refers to a range within the active sheet
  ' .Range("A1:C11") refers to a range within the sheet identified in the
  '         With statement.
  ' ^ Note the dot
  With Sheets("Sheet1")

    ' Rows.Count is the number of rows for the version of Excel you are using.
    ' .Cells(Rows.Count, "A") address the bottom row of column A of worksheet
    ' Sheet1.
    ' .Cells(Rows.Count, 1) refer to column A by number.
    ' End(xlUp) is the VBA equivalent of Ctrl+Up.
    ' If you positioned the cursor at the bottom of column A and pressed
    ' Ctrl+Up, the cursor would jump to the last row in column A with a value.
    ' The following statement gets that row number without actually moving
    ' the cursor.
    RowS01Max = .Cells(Rows.Count, "A").End(xlUp)

    ' The following statement loads the contents of range A1:C11 of
    ' Sheets("Sheet1") into array Sheet1Data.
    Sheet1Data = .Range("A1:C11").Value

    ' This is the same statement but the range is specified in a different way.
    ' .Cells(Row,Column) identifies a single cell within the sheet specified in
    ' the With statement.  .Cells(1,1) identifies row 1, column 1 which is A1.
    '. Cells(11, "C") identifies row 11, column C which is C11.
    Sheet1Data = .Range(.Cells(1, 1), .Cells(11, "C")).Value

    ' This statement uses RowS01Max to specify the last row
    Sheet1Data = .Range(.Cells(1, 1), .Cells(RowS01Max, 1)).Value

    ' In all three examples above, the contents of the specified range will
    ' be loaded to array Sheet1Data.  Whichever range you pick, Sheet1Data
    ' will always be a two dimensional array with the first dimension being
    ' the row and the second dimension being the column.

    ' In the first two examples Sheet1Data(5,3) contains the contents
    ' of cell C5.  In the third example, I have only loaded column A but the
    ' array will still has two dimensions but the only permitted value for the
    ' second dimension is 1.

    ' The following statement writes the contents of Sheet1Data to column "E"

    .Range(.Cells(1, 5), .Cells(RowS01Max, 5)).Value = Sheet1Data

  End With

  With Sheets("Sheet2")

    ' The following statement writes the contents of Sheet1Data to column "E"
    ' of worksheet Sheet2.
    .Range(.Cells(1, 5), .Cells(RowS01Max, 5)).Value = Sheet1Data

  End With

End Sub

Don't despair! Most of us started with the macro recorder and still use it to discover the syntax for an unfamiliar command. Look through other questions. Some ask about exotic functionality but many are about moving data around in, to the experienced programmer, simple ways. Set up some workbooks with the questioner's problem. Copy and paste the solution into a module. Step through it using F8 (see the debugger), switch between Excel and Editor, watch what is happening to the worksheet and move the cursor over a variable to see its current value. Spend half a day playing. You will be amazed at how quickly it starts to make sense. Good luck and good programming.

不要绝望！我们大多数人从宏记录器开始，仍然使用它来发现不熟悉的命令的语法。查看其他问题。有些人询问异国情调的功能，但许多人是关于将数据移入有经验的程序员的简单方法。为提问者的问题设置一些工作簿。将解决方案复制并粘贴到模块中。使用 F8 单步执行（参见调试器），在 Excel 和编辑器之间切换，观察工作表发生的情况并将光标移到变量上以查看其当前值。玩了半天。你会惊讶于它开始变得有意义的速度。祝你好运和良好的编程。

回答by e.James

The following should do the trick:

以下应该可以解决问题：

Sub Refresh_Data()
    Dim lastRow As Integer
    Dim lastCol As Integer
    Dim entireRange As Range
    Dim targetRange As Range

    lastRow = Excel.Evaluate("COUNTA(A:A)") ''// count the rows in column A
    lastCol = Excel.Evaluate("COUNTA(1:1)") ''// count the columns in row 1

    Set entireRange = Range(Cells(1, 2), Cells(lastRow, lastCol))
    Set targetRange = Range(Cells(2, 2), Cells(lastRow, lastCol))

    entireRange.FillDown
    Application.Calculate
    targetRange.Copy
    targetRange.PasteSpecial Paste:=xlPasteValues
End Sub

Notes:

笔记：

Excel.Evaluate(...)allows you to use the result of worksheet functions in your VBA macros.

Excel.Evaluate(...)允许您在 VBA 宏中使用工作表函数的结果。

COUNTA(range)is a worksheet function that counts the number of non-blank cells in a given range. In this case, it can be used to determine the total number of rows in your data set, as well as the number of columns in row 1 that have a formula in them.

COUNTA(range)是一个工作表函数，用于计算给定范围内非空白单元格的数量。在这种情况下，它可用于确定数据集中的总行数，以及第 1 行中包含公式的列数。