delete row based on value of cell in row
 

I am attempting to loop through each row of the used range of a sheet, and
then delete it based on its contents. My code is failing. Any help would
be appreciated. Thanks in advance.

For i = 8 To ActiveSheet.UsedRange.Rows.Count
Set x = Cells(i, 6).value
If Not IsNumeric(x) Then
ActiveSheet.Rows(i).Delete
End If
Next i

delete row based on value of cell in row
 

You create a range object by setting the variable equal to the range,
not to the range value... Set x = Cells(i, 6)

It is good practice to use the default property of objects instead of
letting Excel assume it... If not IsNumeric(x.Value) Then

Delete rows from the bottom up...
For i =ActiveSheet.UsedRange.Rows.Count to 8 Step -1
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"AD108" wrote in message ...
I am attempting to loop through each row of the used range of a sheet, and
then delete it based on its contents. My code is failing. Any help would
be appreciated. Thanks in advance.

For i = 8 To ActiveSheet.UsedRange.Rows.Count
Set x = Cells(i, 6).value
If Not IsNumeric(x) Then
ActiveSheet.Rows(i).Delete
End If
Next i

delete row based on value of cell in row
 

When deleting rows you really should work in reverse, as follows.
Dim lRow as Long
lRow = Cells(Rows.Count,6).End(xlUp).Row
For i = lRow to 8 Step - 1
If Not IsNumeric(Cells(i,6).Value) Then Rows(i).Delete
Next

HTH
Die_Another_Day

AD108 wrote:
I am attempting to loop through each row of the used range of a sheet, and
then delete it based on its contents. My code is failing. Any help would
be appreciated. Thanks in advance.

For i = 8 To ActiveSheet.UsedRange.Rows.Count
Set x = Cells(i, 6).value
If Not IsNumeric(x) Then
ActiveSheet.Rows(i).Delete
End If
Next i

delete row based on value of cell in row
 

Thanks very much. Right, if I start deleting them from the top, the counter
won't work.


"Jim Cone" wrote in message
...
You create a range object by setting the variable equal to the range,
not to the range value... Set x = Cells(i, 6)

It is good practice to use the default property of objects instead of
letting Excel assume it... If not IsNumeric(x.Value) Then

Delete rows from the bottom up...
For i =ActiveSheet.UsedRange.Rows.Count to 8 Step -1
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"AD108" wrote in message
...
I am attempting to loop through each row of the used range of a sheet, and
then delete it based on its contents. My code is failing. Any help would
be appreciated. Thanks in advance.

For i = 8 To ActiveSheet.UsedRange.Rows.Count
Set x = Cells(i, 6).value
If Not IsNumeric(x) Then
ActiveSheet.Rows(i).Delete
End If
Next i

delete row based on value of cell in row
 

Your code is OK except for "Set". The line should be:
x = Cells(i, 6).value
"Set" assigns an object reference to a variable or property. In other
words it is used to give a name to an object.


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=566349