Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

excel

vba

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.

like image 865
gizgok Avatar asked Sep 02 '10 14:09

gizgok


People also ask

How do I find blank rows in Excel VBA?

for those who are intersted to remove "empty" and "blank" rows ( Ctrl + Shift + End going deep down of your worksheet ) .. here is my code. It will find the last "real"row in each sheet and delete the remaining blank rows. Open VBA ( ALT + F11 ), Insert -> Module, Copy past my code and launch it with F5.

How do you check if a cell is blank in Excel VBA?

If you wish to test whether a worksheet cell is empty in VBA, you can not use the worksheet function called ISBLANK. In VBA, you must use the ISEMPTY function. In this example, we will test whether cell A1 is empty. If cell A1 is empty, the message "Cell A1 is empty" will be displayed.


2 Answers

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)

like image 77
GotDibbs Avatar answered Sep 25 '22 05:09

GotDibbs


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
like image 29
Jay Avatar answered Sep 25 '22 05:09

Jay