Deleting blank rows

  • Thread starter Thread starter Yvette
  • Start date Start date
Y

Yvette

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.
 
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
 
Hi Yvette
ScreenUpdate = False
Click to expand...
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.
 
Back
Top