There is no activeworksheet object in excel. It's ActiveSheet.

Another problem is that when you delete rows and start at the top, it becomes a
mess. You'll see this in your testing.

The easiest alternatives are to start at the bottom and work up. Or start at
the top and work down, but build a range that will be deleted at the end:

Option explicit
sub test1()
dim iRow as long
dim LastRow as long

with activesheet.range("a1").currentregion
lastrow = .rows(.rows.count).row
end with

for irow = lastrow to firstrow step -1
if .cells(irow,"A").value = "" then
.rows(irow).delete
end if
next irow
end sub
sub test2()
dim myCell as range
dim myRng as range
dim delRng as range

set myrng = activesheet.range("a1").currentregion.columns(1)

set delrng = nothing
for each mycell in myrng.cells
if mycell.value = "" then
if delrng is nothing then
set delrng = mycell
else
set delrng = union(mycell, delrng)
end if
end if
next mycell

if delrng is nothing then
msgbox "nothing to delete"
else
delrng.entirerow.delete
end if
end sub

Both uncompiled and untested. Watch for typos.

=======
You may find sorting your range by column A and putting the empty cells
together, then deleting that single contiguous range even quicker than using a
macro.



owlnevada wrote:

From the help screens (Worksheet.rows property), I am trying to adapt this
code to do something similar. I have a worksheet of mailing lists with all
the data stripped from a single column(A) to fill columns B,C,D etc with each
item so that now only every 4th row has the data (in cols A:D) and I need to
delete the blank rows (2:4, 6:8, 10:12, etc). Am abit puzzled as to what the
"object required" error needs after defining the rw as object. Am using
Excel 2007.

I need to dimension all the variables with Option Explicit so what am I
missing here? It needs to check each row for no data in case of some 5 line
addresses rather than 4 so all data is preserved and not accidentally deleted.

Thanks in advance for your help.

Sub DeleteRows()

Dim rw As Object

For Each rw In ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows
this = rw.Cells(1, 1).Value
If this = "" Then rw.Delete
last = this
Next

End Sub

--

Dave Peterson