Remove empty rows from a range

  • Thread starter Thread starter Albert Browne
  • Start date Start date
A

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
 
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.
 
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.
 
Back
Top