What is the correct object?

[owlnevada]

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) 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

 

[Susan]

i don't use 07 so there may be a difference here, but if i were doing
what you are doing i would dim rw as range.
if that fixes that problem, then you're going to bomb because you
haven't declared "this" and "last".
hope that helps a little. if not, well, i tried.

susan

 

[Patrick Molloy]

DIM rw as RANGE
DIM this as STRING
DIM last as STRING
----


NOTE in old VB one used to write
DIM this, last as string
and both variables would be type string. However, if you wrote this in VBA,
then the variable 'this' would default to type variant
so
DIM this, last as strting
is in fact
DIM this as variant, last as strting
so to be sure and explicitly dimension every variable's type.

 

[owlnevada]

Still gives an "Object Required" error when I dim as follows"

Sub DeleteRows()

Dim rw As Range
Dim this As String, last As String

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]

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.

 

[JLGWhiz]

If you want to delete only the cells in column A
that are blank, then:

Sub delBlanks()
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Range("A1:A" & lr)
myRange.SpecialCells(xlCellTypeBlanks).Delete
End Sub


If you want to delete entire rows.

Sub delRows()
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Range("A1:A" & lr)
For i = lr To 1 Step -1
If Range("A" & i) = "" Then
Rows(i).Delete
End If
Next
End Sub

 

[Tushar Mehta]

If column A will always have data for those rows you want to keep and always
not have data for those rows you want to delete, use

ActiveSheet.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu

 

[thinfrog4]

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]

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