Remove blank rows

[philb99]

Hi

need help with excel spreadsheet which has my header 2 blank rows row of data then 2 blank rows row of data and this carries on all the way down

is there anyway I can remove the blank rows please

 

[mumps]

Try:

Sub deleteBlankRows()
    Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[JackDanIce]

Couple of ways, one to try (make copy of data first):

Insert a column to the left of the first column of data (if I assume this is column A, then insert so that column A is now column B)
In first row enter formula: "=MAX(LEN(B1),1)*ROW()," and copy formula down, then copy and replace with values
If you now sort your data in ascending order by column A, all the blank rows will be at the top, so you have a single section to delete out.

Or top of this link shows how to delete entire rows using Special cells (F5): http://tinyurl.com/y3ngnnnq

 

[philb99]

Thanks for your help - I presume I don't use the " at the front and end.

I have copied the formula into the created Col A - then Copy and Paste Special - Values - Would that be correct?

If so when I sort Col A, A-Z nothing happens