Delete Blank Rows

[Guest]

Hi, I need to find a way to delete wholly blank rows from a spreadsheet that
also includes blank cells.
I tried F5>special...>radio button Blanks>OK >ctrl+- delete rows , but that
also deletes rows that have data in them along with a blank cell!

Is there an equally neat way of deleting rows that have no data in them at
all, but leaving partial rows?

 

[Guest]

Heather,


Here's a VB solution. Right click the sheet tab, view code and paste this
in. Slect the range you want to delete blank rows in and run the code.

Sub DeleteBlankRows()
Dim i As Long
Application.ScreenUpdating = False
For x = selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(selection.Rows(x)) = 0 Then
selection.Rows(x).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub

Mike

 

[Roger Govier]

Hi Heather

Work on a COPY of your data, just in case!!!

You could use an extra column alongside your data. Enter 1, 2 in the first 2
rows of this column>select the 2 cells>grab the fill handle at bottom right
of cell containing the number 2>fill down as far as you data extends.

Now, Sort the entire block (including your new column) by any column that
must have data to be retained.
All rows which have a blank in this position, will be sorted to the bottom
of the list.
Delete the block of rows at the bottom of the list, then sort again by the
new column to get the data back into it's original order.
Delete the added new column

 

[Guest]

One way - hide rows with data and then delete visible rows.
This is quickest if you use the keyboard shortcuts

1. Select the whole datasheet (Ctrl+A twice) then press...

Shift+Tab (selects last data cell)
Ctrl + \ (Edit > Goto > Special > Row Differences)
Ctrl + 9 (Format > Row > Hide)

2. Select whole datasheet again then:

Alt+; (Edit > Goto > Special > Visible Cells)
Ctrl - (Edit > Delete)

Now select the datasheet and unhide the rows.

 

[Don Guillett]

How about this?
Sub delblankrows()
lr = Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row
On Error Resume Next
For i = lr To 2 Step -1
If Rows(i).Find("*") Is Nothing Then Rows(i).Delete
Next i
End Sub

 

[Guest]

This always works well for me:

Sub DeleteBlankRows1()
'Deletes the entire row within the selection if the ENTIRE row contains no
data.

'We use Long in case they have over 32,767 rows selected.
Dim i As Long

'We turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

'We work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

 

[Guest]

Thank you for all your help, I tried all your solutions but the two that came
out top were Don and Lori. Don's code worked but took a bit of time, Lori's
method was quick and I'm impressed by all the keyboard shortcuts!
I recorded Lori's method and heres a copy of the code. It does have a cell
reference in it, which I moved to Z10000 to cover all my likely requirements,
maybe one of you experts can "generalise" it.

Thanks again,
Heather

Sub HideDeleteReveal()
'
' HideDeleteReveal Macro
' Hides Rows with Data, Deletes Blank Rows, and Reveals Data
'

'
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("A1:Z10000").Select
Range("Z10000").Activate
Selection.RowDifferences(ActiveCell).Select
Selection.EntireRow.Hidden = True
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End Sub

 

[Guest]

I meant to say A1 should be active initially, well done for following the
instructions.
It's actually just three steps on the undo/redo dropdownlist and should be
recordable without a fixed range. Here's a reduced version:

Sub HideDeleteReveal()

Cells.ColumnDifferences(Range("IV65536")).EntireRow.Hidden = True
Cells.SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
Cells.EntireRow.Hidden = False

End Sub

 

[Don Guillett]

Cleaned up
lr = Range("a1").SpecialCells(xlLastCell).Row
lc = Range("a1").SpecialCells(xlLastCell).Column

With Range(Cells(1, 1), Cells(lr, lc))
.RowDifferences(Cells(lr, lc)).EntireRow.Hidden = True
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Rows.Hidden = False
End With

 

[Mark]

Perfect - this code did exactly what I needed it for! Thanks!