Delete Blank rows

[Sige]

Hi There,

Chip's code to delete blank rows.
Situation:

If my first data starts appears in row 5 and row 1-4 are blank, it will
delete all blank lines but does not delete row 1-4. They are not part
of the usedrange apparently.
(I always thought usedrange = A1:LAST CELL, but seems to be FIRST
CELL:LASTCELL)

Can I get rid of those lines as well?

Cheers Sige

Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[William]

Hi Sige

Try defining the range as...
Set Rng = Range("A1", Cells.SpecialCells(xlCellTypeLastCell)).entirerow

--


XL2003
Regards

William
(e-mail address removed)

 

[Sige]

Thx William,
Much Better!!

 

[Norman Jones]

Hi Sige,

(I always thought usedrange = A1:LAST CELL, but seems to be FIRST
CELL:LASTCELL)

In fact the usedrange starts at the first populated or formatted cell.

In an unused worksheet, format or populate a cell other than A1. In the
intermediate window type:
? UsedRange.Address
and see the result.

Can I get rid of those lines as well?

Try replacing:

For R = Rng.Rows.Count To 1 Step -1

with

For R = Rng.Row + Rng.Rows.Count - 1 To 1 Step -1

 

[Sige]

Perfect!
Sige

 

[Gary Keramidas]

why do i get an object required error when i try this, ? UsedRange.Address,
in the immediate window?

 

[Norman Jones]

Hi Gary,

Should be:

?ActiveSheet.UsedRange.Address

I typed rather than copy / pasted!

Thank you.

 

[Ron de Bruin]

Hi Gary

You must use it like this

? activesheet.UsedRange.Address

 

[Tom Ogilvy]

Maybe in the "Intermediate" window you don't need to qualify it <g>. I
don't have one, so I can't say.