# Excel Macro to remove Blank Rows



## TechTory (Jan 7, 2011)

I need help with a Macro that will delete multiple BLANK rows between data on a spreadsheet. I have used a Macro, but it deletes all blank rows. I want 1 Blank Row between each set of like data rows, instead of the three between some.

This macro would delete 2 rows where Consecutive Blank Row Count = 3 but
Will leave single Blank Row where there is a single Blank Row between like data.

Example: 
Row1ColumnA 01 / Row1ColumnF AB 
Row2ColumnA 01 / Row2ColumnF AB Row3 = Blank
Row4 = Blank
Row5 = Blank
Row6ColumnA 02 / Row6ColumnF AC 
Row7 = Blank Row8ColumnA 02 / 
Row8ColumnF AD Row9ColumnA 02 / 
Row9ColumnF AD
Row10 = Blank
Row11 = Blank
Row12 = Blank


----------



## CliveO (Aug 6, 2011)

I think this will work as long as you have less than 10000 rows although I haven't tried it out. It would be quit easy to convert to a Do / Loop which would stop after a certain number of blank rows.
Sub DelBlanksRows()
For i = 0 to 10000
if Range ("A1").offset(i,0)="" then
if Range ("A1").offset(i+1,0)="" then
Range("A1").offset(i+1,0).select
Selection.EntireRow.Delete 
Else
i=1+1
End if
i=1+1
End if
Next i
End Sub


----------

