☰

Excel 中空行的消息框

tag-icon • tag-icon microsoft-excel vba macros
Excel 中空行的消息框

我有一个很大的 excel，里面有很多行，将来可能会增加，所以我想确定方法，如果单击按钮，我可以检查任何字段是否为空，我尝试使用下面的宏来检查单元格值，它有效，但如何选择所有列及其单元格来检查空值

Sub ProcFile()
Dim wsRaw As Worksheet: Set wsRaw = ThisWorkbook.Sheets("Shell2")

Dim iRow, x, LRow, sRow, col As Long

LRow = getLastRow(wsRaw, "A")

If wsRaw.Range("A6").Value = "" Then MsgBox "Raw Data tab is Empty!!", vbCritical: Exit Sub

End Sub

答案1

修改以下代码。它获取第 1 行中的最后一个非空白单元格（假设该行的内容是标题）。这允许获取最后一列。然后循环遍历每一列和每一行（如果需要）。

Sub Loop_Column_Row()
    
    Dim lRow, lCol As Long
    
    'Find the last non-blank cell in row 1
    'This assumes that the first row has column headers
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    'Loop through columns
    For x = 1 To lCol
        'Find the last non-blank cell in the column
        lRow = Cells(Rows.Count, x).End(xlUp).Row
    
        'Loop through rows
        'Start from row 2 as row 1 is the row with headers
        For y = 2 To lRow
            If Cells(y, x) = "" Then
                'Display message box when empty cell is found
                MsgBox "Cell in Row: " & y & " Column: " & x & " is empty"
                'Stop executing the method when 1st empty cell found
                Exit Sub
            End If
        Next y
    Next x
  
End Sub

相关内容