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

Deleting blank rows
 

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

Deleting blank rows
 

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).E ntireRow) = 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" wrote in message
...
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" wrote in message
...
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

Deleting blank rows
 

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" wrote in message
...
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).E ntireRow) = 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" wrote in message
...
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" wrote in message
...
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