How to find out if an entire row is blank in excel thorough vba

Question

I have a sheet in which I have data from two different sources.I've a blank row between them.I want to make this blank row as my delimiter.How can I find out if the entire row is blank or not.

Answer 1

No need to use VBA for that, you can use the filter feature instead. Just follow these steps:

1 - Select all the headings/row for your columns you want to apply the filter to.
2 - Click Data from menu (office 2003) or button (office 2007) and then click Filter
3 - You will see a list of filters, scroll down at the bottom and select Blanks

Repeat the same process for all your columns and you have empty rows filtered out :)

Answer 2

If you're talking a literal entire row then code similar to this should work (so long as there are no formulas or spaces present in any of the cells as well):

If Application.CountA(ActiveCell.EntireRow)=0 Then
     MsgBox "Row Empty"
     Exit Sub
End If

Otherwise, for a range from a row:

Dim neValues As Range, neFormulas As Range, MyRange As Range

Set MyRange = Columns("C:AA")

On Error Resume Next
Set neValues = Intersect(ActiveCell.EntireRow.SpecialCells(xlConstants), MyRange)
Set neFormulas = Intersect(ActiveCell.EntireRow.SpecialCells(xlFormulas), MyRange)
On Error GoTo 0

If neValues Is Nothing And neFormulas Is Nothing Then
    MsgBox "Nothing There"
Else
    MsgBox "Something's There"
End If

(Source: http://www.ozgrid.com/forum/showthread.php?t=26509&page=1)

Answer 3

WorksheetFunction.CountA(), as demonstrated below:

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