Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete ROW based on Cell Contents | Excel Discussion (Misc queries) | |||
Delete row based on cell value | Excel Discussion (Misc queries) | |||
delete row on based on cell value | Excel Discussion (Misc queries) | |||
Delete Row based off cell content | Excel Discussion (Misc queries) | |||
Delete Row based on cell contents | Excel Programming |