Hello

There is probably an easy solution to this that I have overlooked, but...

i have written a Routine in VBA that consolidates data from 70 user spreadsheets into a master file every evening. The extraction runs OK, but the master file contains sporadic blank rows that are being imported from the user sheets. Thats all fine, but these blank rows cause other routines that call the master file to fail. Is there a method in VBA that can check through a range (c. 5000 rows), select entire blank rows, and delete them? Thanks in advance for any ideas.

Using For...Next Loops to test for "" in a cell on a row isn't really a reliable option because there can be data in any column from A to AQ on a row. I'm only interested in entirely blank rows.

I also tried "Selection.SpecialCells(xlCellTypeBlanks).Sele ct" - no good as this will also select the valid rows. This really is driving me crazy - i'd appreciate a fresh perpective!

Excel 97 - I'll post the code if requested.

I found this on the web somewhere. It might be usefull but slow. You'll have to select the entire area that's to be tested, not only a row. I'v changed the action to colorize for testing purpose:

Sub DeleteBlankRows()
Dim i As Long

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).Interior.ColorIndex = 6
'Selection.Rows(i).EntireRow.Delete
End If
Next i

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
 End With
End Sub

Hope you can develop on it!

Take a look at this address:

http://www.mvps.org/dmcritchie/excel/delempty.htm

Thanks Birger for the pointers - I'll see what i can uncover.