Delete Blank rows

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

Hi Sige

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

--


XL2003
Regards

William
willwest22@yahoo.com


"Sige" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
news:1127813229.825765.314560@g14g2000cwa.googlegroups.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).EntireRow) = 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" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
news:1127813229.825765.314560@g14g2000cwa.googlegroups.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).EntireRow) = 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" <normanjones@whereforartthou.com> wrote in message
news:uwNRA50wFHA.720@TK2MSFTNGP10.phx.gbl...
> 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" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
> news:1127813229.825765.314560@g14g2000cwa.googlegroups.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).EntireRow) = 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" <GKeramidas@msn.com> wrote in message
news:%23Zfb9q2wFHA.3720@TK2MSFTNGP14.phx.gbl...
> why do i get an object required error when i try this, ?
> UsedRange.Address, in the immediate window?
>
> --
>
>
> Gary
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:uwNRA50wFHA.720@TK2MSFTNGP10.phx.gbl...
>> 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" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
>> news:1127813229.825765.314560@g14g2000cwa.googlegroups.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).EntireRow) = 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" <GKeramidas@msn.com> wrote in message news:%23Zfb9q2wFHA.3720@TK2MSFTNGP14.phx.gbl...
> why do i get an object required error when i try this, ? UsedRange.Address, in the immediate window?
>
> --
>
>
> Gary
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message news:uwNRA50wFHA.720@TK2MSFTNGP10.phx.gbl...
>> 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" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message news:1127813229.825765.314560@g14g2000cwa.googlegroups.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).EntireRow) = 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" <normanjones@whereforartthou.com> wrote in message
news:OEVPhA3wFHA.2072@TK2MSFTNGP14.phx.gbl...
> Hi Gary,
>
> >> ? UsedRange.Address
>
> Should be:
>
> ?ActiveSheet.UsedRange.Address
>
> I typed rather than copy / pasted!
>
> Thank you.
>
> ---
> Regards,
> Norman
>
>
>
> "Gary Keramidas" <GKeramidas@msn.com> wrote in message
> news:%23Zfb9q2wFHA.3720@TK2MSFTNGP14.phx.gbl...
> > why do i get an object required error when i try this, ?
> > UsedRange.Address, in the immediate window?
> >
> > --
> >
> >
> > Gary
> >
> >
> > "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> > news:uwNRA50wFHA.720@TK2MSFTNGP10.phx.gbl...
> >> 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" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
> >> news:1127813229.825765.314560@g14g2000cwa.googlegroups.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).EntireRow) = 0
> >>> Then
> >>> Rng.Rows(R).EntireRow.Delete
> >>> End If
> >>> Next R
> >>>
> >>> EndMacro:
> >>>
> >>> Application.ScreenUpdating = True
> >>> Application.Calculation = xlCalculationAutomatic
> >>>
> >>> End Sub
> >>>
> >>
> >>
> >
> >
>
>