Deleting blank rows

Hi ppl,

After pasting and sorting say A1:F20 any blanks always end up top when using
an acsending sort.

How can I (using code) delete any blank rows.

TIA

Yvette

Hi Yvette

If the "blank" rows are sorting to the top, then they are not blank but
contain spaces. Blank rows will automatically fall to the bottom of a
sorted list.
Try sorting descending first, then delete all rows below your data.
Then sort ascending again.

--
Regards

Roger Govier


"Yvette" <yvette@basslink.net.au> wrote in message
news:elJ$hjgiGHA.3848@TK2MSFTNGP04.phx.gbl...
> Hi ppl,
>
> After pasting and sorting say A1:F20 any blanks always end up top when
> using an acsending sort.
>
> How can I (using code) delete any blank rows.
>
> TIA
>
> Yvette
>

Hi Roger

Thx for your reply, I got hold of some code but I can't seem to get it to
work, maybe you, or someone are familiar with it:

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

ScreenUpdate = False
Application.Calculation = xlCalculationManual

On Error GoTo EndMacro

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

In setting the (Rng) is that the Cell, eg A20, if not then how do I specify
the blank starting row I want to delete

TIA

Yvette

"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:ekj8D3giGHA.4304@TK2MSFTNGP03.phx.gbl...
> Hi Yvette
>
> If the "blank" rows are sorting to the top, then they are not blank but
> contain spaces. Blank rows will automatically fall to the bottom of a
> sorted list.
> Try sorting descending first, then delete all rows below your data.
> Then sort ascending again.
>
> --
> Regards
>
> Roger Govier
>
>
> "Yvette" <yvette@basslink.net.au> wrote in message
> news:elJ$hjgiGHA.3848@TK2MSFTNGP04.phx.gbl...
>> Hi ppl,
>>
>> After pasting and sorting say A1:F20 any blanks always end up top when
>> using an acsending sort.
>>
>> How can I (using code) delete any blank rows.
>>
>> TIA
>>
>> Yvette
>>
>
>

Hi Yvette

> ScreenUpdate = False
should be Application.ScreenUpdating = False

You can either mark a range of rows before invoking the macro, or, if
not it will look at the whole used range on the sheet.
The macro examines each row starting from the last row found with data,
back to the beginning of the data range, and uses the COUNTA function to
determine if there are nay entries in the row. If there are not
(Counta=0) then it deletes that entire row.

--
Regards

Roger Govier


"Yvette" <yvette@basslink.net.au> wrote in message
news:ua$3vc5iGHA.4044@TK2MSFTNGP03.phx.gbl...
> Hi Roger
>
> Thx for your reply, I got hold of some code but I can't seem to get it
> to work, maybe you, or someone are familiar with it:
>
> Dim R As Long
> Dim C As Range
> Dim Rng As Range
>
> ScreenUpdate = False
> Application.Calculation = xlCalculationManual
>
> On Error GoTo EndMacro
>
> 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
>
> In setting the (Rng) is that the Cell, eg A20, if not then how do I
> specify the blank starting row I want to delete
>
> TIA
>
> Yvette
>
> "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:ekj8D3giGHA.4304@TK2MSFTNGP03.phx.gbl...
>> Hi Yvette
>>
>> If the "blank" rows are sorting to the top, then they are not blank
>> but contain spaces. Blank rows will automatically fall to the bottom
>> of a sorted list.
>> Try sorting descending first, then delete all rows below your data.
>> Then sort ascending again.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Yvette" <yvette@basslink.net.au> wrote in message
>> news:elJ$hjgiGHA.3848@TK2MSFTNGP04.phx.gbl...
>>> Hi ppl,
>>>
>>> After pasting and sorting say A1:F20 any blanks always end up top
>>> when using an acsending sort.
>>>
>>> How can I (using code) delete any blank rows.
>>>
>>> TIA
>>>
>>> Yvette
>>>
>>
>>
>
>