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

2 回答

• 3

  虽然早期的答案有其优点：

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

  2）在运行宏之前，我永远不会依赖用户选择要使用的正确表单;特别是有这么多床单的人 .

  我对这个问题的反应是你已经设置了Macro Record，在你的工作簿上闲逛然后停止了记录 . 你选择一件事，然后选择另一件事 . 滚动页面 . 对我来说，大多数陈述都不笨拙，毫无意义 .

  以下内容确实包含了有关查找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
  

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

  回复于 2024-05-04T07:22:55+08:00
• 10

  以下应该做的伎俩：

  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
  

  笔记：

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

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

  回复于 2024-05-04T07:22:55+08:00