Andreas Hermle
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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...
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
Again, thank you very much for your superb and professional help.
Regards, Andreas
Open in new window