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).E ntireRow) = 0
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Hi Sige

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

--


XL2003
Regards

William



"Sige" wrote in message
oups.com...
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).E ntireRow) = 0
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Thx William,
Much Better!!

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


---
Regards,
Norman



"Sige" wrote in message
oups.com...
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).E ntireRow) = 0
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Perfect!
Sige

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

--


Gary


"Norman Jones" wrote in message
...
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


---
Regards,
Norman



"Sige" wrote in message
oups.com...
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).E ntireRow) = 0
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Hi Gary,

? UsedRange.Address

Should be:

?ActiveSheet.UsedRange.Address

I typed rather than copy / pasted!

Thank you.

---
Regards,
Norman



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

--


Gary


"Norman Jones" wrote in message
...
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


---
Regards,
Norman



"Sige" wrote in message
oups.com...
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).E ntireRow) = 0
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

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

--
Regards,
Tom Ogilvy

"Norman Jones" wrote in message
...
Hi Gary,

? UsedRange.Address

Should be:

?ActiveSheet.UsedRange.Address

I typed rather than copy / pasted!

Thank you.

---
Regards,
Norman



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

--


Gary


"Norman Jones" wrote in message
...
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


---
Regards,
Norman



"Sige" wrote in message
oups.com...
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).E ntireRow) = 0
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Hi Gary

You must use it like this

? activesheet.UsedRange.Address


--
Regards Ron de Bruin
http://www.rondebruin.nl


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

--


Gary


"Norman Jones" wrote in message ...
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


---
Regards,
Norman



"Sige" wrote in message oups.com...
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).E ntireRow) = 0
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub