Geek asks and answers

Excel vba needs a control that checks if an empty cell is

    Private Function test()
    Dim S1 As String, S2 As String

    Dim lRow As Long, i As Long
    Dim ws As Worksheet

    Set ws = ActiveWorkbook.Sheets("pppdp")
    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = 19 To lRow
    End Function

Hi, I have an excel worksheet for exporting a table to xml, so I need a control that will check for an empty cell in the range from A to R, I started from this, but I stopped and don't know what to do next.

+3
source share
1 answer

is there an empty cell in the range from A to R?

The logic is to check if the total number of cells matches the full filled cells.

Like this?

Sub Sample()
    If test = False Then
        MsgBox "Range A to R has empty cells"
    Else
        MsgBox "No Empty Cells"
    End If
End Sub

Private Function test() As Boolean
    Dim lRow As Long
    Dim ws As Worksheet

    Set ws = ActiveWorkbook.Sheets("pppdp")

    With ws
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Range("A:R").Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 1
        End If

        test = Application.WorksheetFunction.CountA(.Range("A1:R" & lRow)) _
             = .Range("A1:R" & lRow).Cells.Count
    End With
End Function

enter image description here

+1
source

More articles:


All Articles