This code runs fine with no errors but doesn't do a thing. Not certain why????

"thinfrog4" wrote:

Hi,

Looking at your example code, the object error that you receive is most
likely because you haven't created and Set the row object itself.

Sub DeleteRows()

Dim rw As Object

Set rw = ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows

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

End Sub

Set rw = ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows

The line using the Set keyword will establish the nature of the object in
the loop. When starting the Loop, the .rows after CurrentRegion should not be
present as the nature of rw implies this.

This is my first post so please don't pounce, but I do hope that this helps.

Dave

"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