Remove empty rows from a range

[Albert Browne]

Hi,


I have a range on a worksheet E7:M106. When certain conditions are met a row
in the range is moved to another worksheet.
Alternate rows are formatted with a different colour which needs to be
preserved. What would be the best way to move all the other rows up to
close this gap?

Thanks,

Albert

 

[RyanH]

This should help! This procedure will delete all blank rows based on blank
cells that it detects in Col.A. Note: It is always good practice to setup a
copy of the workbook you are wanting to run this procedure to test, before
deleting live data.

Sub DeleteBlankRows()

' delete all blank rows, if no blank rows error occurs
On Error Resume Next
Sheets("Sheet1").Range("A3:A" & Cells(Rows.Count,
"A").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

End Sub

Hope this helps! If so, click "YES" below.

 

[Ron de Bruin]

Warning

See
http://www.rondebruin.nl/specialcells.htm

 

[RyanH]

interesting, that is good info to have! The data I use it for is only 400
rows long, but if something where to accidently happen and blow the Usedrange
up then I could be in trouble. I guess my users will have to wait a few
seconds longer to complete my macro.