excel - 在工作表上找到第一个完全空的行

Question

需要一个函数，从包含稀疏填充单元格的工作表中返回第一个完全空的行(没有值、没有公式、没有空格)。无需填写任何一栏。

我尝试过这个，但我什至可以编译它:

Public Donations As Worksheet
Set Donations = Sheets("Sheet2")

Function getEmptyRow() As Long
   Dim lastCol As Long, lastRow As Long, maxRow As Long
   Dim col As Long
   Dim r As Variant

   lastCol = Donations.Cells(1, Columns.Count).End(xlToLeft).Column
   For col = 1 To lastCol Step 1
      lastRow = Donations.Cells(Rows.Count, col).End(xlUp).row
      maxRow = Application.WorksheetFunction.max(maxRow, lastRow)
   Next col
   getEmptyRow = maxRow + 1
End Function

Answer 1

使用 EntireRow(这非常有用，让我告诉你)并从 A1 开始逐行计数是执行此操作的一种非常基本的方法。

这将在立即窗口中告诉您:

Sub findemptyrow()                       '''Psuedo Code
Application.ScreenUpdating = False       'turns off annoying blinky
Range("a1").Activate                     'start at beginning   
While a <> 1                             'keep going
  If Application.CountA(ActiveCell.EntireRow) = 0 Then  'is it blank?
  Debug.Print "Row " & (ActiveCell.Row) & " is blank."   'it is
  a = 1                                   'stop going  
  End If
  ActiveCell.Offset(1).Activate           'next cell
Wend                                      'do it all over again  
Application.ScreenUpdating = True         'back to normal settings
End Sub

将 ScreenUpdating 设置为 False 将使速度更快，即使有 10k 行。