Pls help: macro to delete empty rows

[curiousgeorge408]

I have a worksheet with over 1100 rows, but with data only in every
few rows. The other rows are "empty" (ISBLANK is true). I need a
macro to delete the empty rows.

Using Record Macro, I see that if I select a row and right-click
Delete, the logic is:

Rows("2:2").Select
Selection.Delete shift:=xlUp

So in my ignorance, I tried the following, to no avail:

Dim cell As Range
For Each cell In Range("1:1144")
If cell = "" Then
cell.Rows.Delete shift:=xlUp
End If
Next

So how can I do this?

PS: Also, I forgot how to turn off/on the worksheet update. And if
I need to do this iteratively, as above, I would like to go from the
bottom up so that the shift xlUp is more efficient.

Many thanks.

 

[Barb Reinhardt]

Dim cell As Range
For Each cell In Range("1:1144") 'You're looking at all cells in rows
1:1144.
If cell = "" Then
cell.Rows.Delete shift:=xlUp
End If
Next

Try this

Dim myRange as range
dim myCell as range
set myRange = Range("A1:A100") 'or whatever you want

Dim myDeleteRange as range

for each myCell in myRange
if Isempty(mycell) then
if myDeleteRange is nothing then
Set myDeleteRange = myCell
else
Set myDeleteRange = union(mydeleterange,mycell)
end if
end if

next mycell

If not myDeleteRange is nothing then
myDeleteRange.entireRow.delete
end if

Untested ... (just typed in here)
--


HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[JLGWhiz]

Sub tract()
Dim lr As Long, lc As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
For i = lr To 2 Step -1
If WorksheetFunction.CountA(Range(Cells(i, 1), Cells(i, lc))) _
= 0 Then
Rows(i).Delete
End If
Next
End Sub

 

[curiousgeorge408]

HTH

Sure did! Thanks.

Untested ... (just typed in here)

Worked perfectly as is. Thanks much.

One minor tweak, if I may ask: How can I reset where ctrl-End goes
(i.e. that last row/column of real data) after entireRow.delete
shifts all the real data up?

I vaguely remember that there is an "obvious" method for doing that in
VBA. But I cannot find the information at the moment.

PS: I caused the reset by saving and reopening the file. But I'd
like to know how to do it in VBA for the future.

Thanks again.


---- original posting -----

 

[Avi]

One minor tweak, if I may ask: How can I reset where ctrl-End goes

(i.e. that last row/column of real data) after entireRow.delete
shifts all the real data up?

Just save the workbook with Activeworkbook.Save. It will reset the
last used cell.

If you want to proceed further without saving, you can use refer to
cell Cells(ActiveSheet.UsedRange.Rows.Count ,
ActiveSheet.UsedRange.Columns.Count) to refer to the last cell.

~AV