How to delete blank rows?

[Joe Kickass]

hi guys,

I have a spreadsheet similar to the (poor) example below...

Basically, i need to delete the rows that do not contain any data
(i.e. rows 1, 6 & 9)

A B C D E F
1
2 OK OK OK OK
3 OK OK
4 OK OK OK OK
5 OK
6
7 OK OK OK OK OK
8 OK OK OK OK
9
10 OK OK OK OK OK

What I'm trying to do at the moment is select ("A1:F1") and find out if there is any data in these cells, if not delete the row then, move down a row and repeat.

Can anyone suggest an easy way to do this please??

Thanks!

 

[Andrew Poulsom]

You could AutoFilter column A for Blanks and delete the visible rows. Or you could use the formula:

=COUNTA(A1:F1)=0

AutoFilter for TRUE and delete the visible rows.

 

[VoG]

Try this: ALT + F11 to open the Visual Basic Editor, Insert > Module and paste in

Sub DelBl()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).Delete
Next i
End Sub

Press ALT + Q to return to your sheet, Tools > Macro > Macros, click on DelBl then click the Run button.

 

[Joe Kickass]

Thanks guys!!

 

[Joe Kickass]

OK, this isn't working and i think it is because my first example wasn't clear enough.

Is there any way that i can paste a section of my Excel spreadsheet in here?

 

[VoG]

You should be able to copy from your sheet and paste here (it will be clearer if you have cell borders) or you can use Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[Andrew Poulsom]

If you choose the Board's Enhanced Message Editor Interface you can copy an Excel range and paste it into a reply. To do so click User CP on the blue bar at the top of this window, then click Edit Options and scroll to the bottom.