PDA

View Full Version : Solved: Delete Empty Rows



StingRay
04-07-2007, 11:30 PM
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
04-08-2007, 03:22 AM
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

mdmackillop
04-08-2007, 03:47 AM
or possibly
If Application.CountBlank(.Cells(i, "A").Resize(, 20)) = 20 Then
.Cells(i, "A").Resize(, 20).Delete Shift:=xlUp
End If

if columns greater than T are to be preserved.

Aussiebear
04-08-2007, 04:21 AM
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
04-08-2007, 04:41 AM
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
04-08-2007, 04:47 AM
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?




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

mdmackillop
04-08-2007, 05:44 AM
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
04-08-2007, 07:34 AM
Many thanks to all of you!

I now have a much improved personal KB -

StingRay:clap: :bow:

Bob Phillips
04-08-2007, 08:28 AM
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
04-08-2007, 08:52 AM
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
04-08-2007, 08:59 AM
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
04-08-2007, 09:09 AM
Thanks Stingray,
A lot of questions are open to interpretation, so different solutions may be offered.