Link to home
Start Free TrialLog in
Avatar of Andreas Hermle
Andreas HermleFlag for Germany

asked on

Tweaking a macro: Detecting blank rows using VBA

Dear Experts:

below code detects empty rows on the Active Sheet's used range. The blank rows are displayed one at a time in a msgbox.

Could somebody help me re-write this code with the following requirements:

Firstly:
The blank rows are to be displayed in just one message box, such as: Rows 5, 7, 9 are empty
Secondly:
If no blank rows are detected the macro has to call up another macro called 'Lookup_My_Range'

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Sub EmptyRow()

Dim row As Range
Dim sheet As Worksheet
Set sheet = ActiveSheet

For i = 1 To sheet.UsedRange.Rows.Count

    Set row = sheet.Rows(i)
    If WorksheetFunction.CountA(row) = 0 Then
        MsgBox "row " & i & " is empty"
    End If

Next i

End Sub

Open in new window

Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

For i = 1 To sheet.UsedRange.Rows.Count

    Msg = "Row "
    Set row = sheet.Rows(i)
    If WorksheetFunction.CountA(row) = 0 Then
        Msg = Msg & row & " is empty" & chr(10)
    End If

Next i
MsgBox Msg

Open in new window

SOLUTION
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sub EmptyRow()

Dim row As Range
Dim i As Long
Dim sheet As Worksheet
Dim msg As String
Set sheet = ActiveSheet

For i = 1 To sheet.UsedRange.Rows.Count

    Set row = sheet.Rows(i)
    If WorksheetFunction.CountA(row) = 0 Then
        msg = msg & "row " & i & " is empty" & vbCrLf
    End If

Next i
MsgBox msg
End Sub

Open in new window

Andres,

A quick question, Now i understand your checking for an empty row by looking at the entire row..But if you can help me understand if i check a particular cell or column like for instance if B Column is empty the row will be empty is that the case is true??

Saurabh...
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Andreas Hermle

ASKER

Dear all,

thank you very much for your overwhelming support. I really appreciate the time taken and your professional help.

Rob and Martin: great coding, thank you very much for it. I am afraid to tell you that one of my requirements was calling up a specific macro if no blank rows are detected. You to inadvertently missed that.

Glenn: exactly as I wanted it. Thank you very much for it.

Professor JimJam: uups, sorry I was not aware of this. Thank you very much for bringing this to my attention and thank you very much for your wonderful code. Fantastic.
I am really deeply impressed by  your expertise and as for Martin and Rob, I knew that you could have done the same coding as Glenn, but you inadvertently missed my second requirement.

Again, thank you very much for your superb and professional help.

Regards, Andreas