Solved: Delete Empty Rows

[StingRay]

I have seen a bazillion versions of this very popular sub, which satisfy multitudes of criteria. My compliments to those who are clever enough to figure them out.

My needs are quite simple: delete all empty rows between (say) A1 and T1000.

Any help would be greatly appreciated!

Thanks -

Sting Ray

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
With ActiveSheet

For i = 1000 To 1 Step -1

If Application.CountBlank(.Cells(i, "A").Resize(, 20)) = 20 Then
.Rows(i).Delete
End If

Next i

End With

End Sub
[/vba]

[mdmackillop]

or possibly
[VBA] If Application.CountBlank(.Cells(i, "A").Resize(, 20)) = 20 Then
.Cells(i, "A").Resize(, 20).Delete Shift:=xlUp
End If
[/VBA]
if columns greater than T are to be preserved.

[Aussiebear]

isn't this similar to that which you indicated in another thread MD? What Bob has written is very close to that which you said was a preferred way.

Delete from the right or in this case from the bottom.

[mdmackillop]

Hi Ted,
No difference in the methodology, but the OP does not say he wants to delete entire rows

My needs are quite simple: delete all empty rows between (say) A1 and T1000.

I have quite a few spreadsheets with many rows in say, columns A:H, summarised in columns N1:P30. Although I may want to delete all blank rows in A:H,. I need to preserve all my summary area, so restrict the deletion using the resize function.

[Aussiebear]

Yes I agree, for the op has indicated the size of the range A1:T1000.
hence the use of the .Resize(, 20) to restrict the range to column T right?

[vba]

Public Sub ProcessData()
Dim i As Long
With ActiveSheet

For i = 1000 To 1 Step -1

If Application.CountBlank(.Cells(i, "A").Resize(, 20)) = 20 Then
.Rows(i).Delete
End If

Next i

End With

End Sub
[/vba]

[mdmackillop]

Bob's code will delete entire rows
.rows(i).delete

My code will delete only from columns A:T
.Cells(i, "A").Resize(, 20).Delete Shift:=xlUp

Either could be correct for the OP.

See the difference.

[StingRay]

Many thanks to all of you!

I now have a much improved personal KB -

StingRay

[Bob Phillips]

Bob's code will delete entire rows
.rows(i).delete

My code will delete only from columns A:T
.Cells(i, "A").Resize(, 20).Delete Shift:=xlUp

Either could be correct for the OP.

See the difference.

He may not have said delete entire rows, but probably because that nuance did not occur to him, whereas he did say delete rows, not delete cells.

[mdmackillop]

What would the result be if he had said "delete all empty cells between (say) A1 and T1000."
I did delete rows, not cells, but limited to the specified range.

[StingRay]

Being a Newbie, I was not aware of all the nuances! Since my worksheet is quite small (Columns A thru T, Rows 1 thru 1,000), I only needed an economical solution.

Thanks Again to All -

StingRay

[mdmackillop]

Thanks Stingray,
A lot of questions are open to interpretation, so different solutions may be offered.