Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows for values
Hello,
I have code that I hoped would iterate through a column and delete rows if values did not begin with a 7. For Each rngDBSingleCostElemCell In wksSheet.Range("C2:C" & lCostElemItemLastRow) If Left(rngDBSingleCostElemCell, 1) < 7 Then rngDBSingleCostElemCell.Rows.Delete End If Next rngDBSingleCostElemCell The values in column C are numbers stored as text. For some reason rows with values like 16101, 16151 remain. thanks for any help. Jake |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows for values
Hi, Highlight column C and apply text to columns "Jake" wrote: Hello, I have code that I hoped would iterate through a column and delete rows if values did not begin with a 7. For Each rngDBSingleCostElemCell In wksSheet.Range("C2:C" & lCostElemItemLastRow) If Left(rngDBSingleCostElemCell, 1) < 7 Then rngDBSingleCostElemCell.Rows.Delete End If Next rngDBSingleCostElemCell The values in column C are numbers stored as text. For some reason rows with values like 16101, 16151 remain. thanks for any help. Jake |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows for values
Doesn't that only work for cells formatted as General in which the number is preceded with an apostrophe? I think cells formatted as Text will remain as Text after doing what you suggested. -- Rick (MVP - Excel) "Eduardo" wrote in message ... Hi, Highlight column C and apply text to columns "Jake" wrote: Hello, I have code that I hoped would iterate through a column and delete rows if values did not begin with a 7. For Each rngDBSingleCostElemCell In wksSheet.Range("C2:C" & lCostElemItemLastRow) If Left(rngDBSingleCostElemCell, 1) < 7 Then rngDBSingleCostElemCell.Rows.Delete End If Next rngDBSingleCostElemCell The values in column C are numbers stored as text. For some reason rows with values like 16101, 16151 remain. thanks for any help. Jake |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows for values
I think you are falling victim to the removals that are taking place. When you delete Row 1, all the other rows move up so that what was Row 2 is not Row 1, etc. If Row2 had 16101 in it, that value would be in Row 1 after the original Row 1 was deleted; but the For..Next loop already processed Row 1, so it will not go back to it again. The way most people avoid this problem is to iterate the loop row-by-row from the last row with data backwards to the first row; that way, deletions do not affect the loop's processing order. Try this (off the top of my head)... With wksSheet For X = lCostElemItemLastRow To 2 Step -1 If Left(.Cells(X, 1).Value, 1) < 7 Then .Cells(X, 1).EntireRow.Delete Next End With -- Rick (MVP - Excel) "Jake" wrote in message ... Hello, I have code that I hoped would iterate through a column and delete rows if values did not begin with a 7. For Each rngDBSingleCostElemCell In wksSheet.Range("C2:C" & lCostElemItemLastRow) If Left(rngDBSingleCostElemCell, 1) < 7 Then rngDBSingleCostElemCell.Rows.Delete End If Next rngDBSingleCostElemCell The values in column C are numbers stored as text. For some reason rows with values like 16101, 16151 remain. thanks for any help. Jake |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows for values
Thanks Rick! Workin' great now. "Rick Rothstein" wrote: I think you are falling victim to the removals that are taking place. When you delete Row 1, all the other rows move up so that what was Row 2 is not Row 1, etc. If Row2 had 16101 in it, that value would be in Row 1 after the original Row 1 was deleted; but the For..Next loop already processed Row 1, so it will not go back to it again. The way most people avoid this problem is to iterate the loop row-by-row from the last row with data backwards to the first row; that way, deletions do not affect the loop's processing order. Try this (off the top of my head)... With wksSheet For X = lCostElemItemLastRow To 2 Step -1 If Left(.Cells(X, 1).Value, 1) < 7 Then .Cells(X, 1).EntireRow.Delete Next End With -- Rick (MVP - Excel) "Jake" wrote in message ... Hello, I have code that I hoped would iterate through a column and delete rows if values did not begin with a 7. For Each rngDBSingleCostElemCell In wksSheet.Range("C2:C" & lCostElemItemLastRow) If Left(rngDBSingleCostElemCell, 1) < 7 Then rngDBSingleCostElemCell.Rows.Delete End If Next rngDBSingleCostElemCell The values in column C are numbers stored as text. For some reason rows with values like 16101, 16151 remain. thanks for any help. Jake |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows with duplicated values | Excel Discussion (Misc queries) | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
delete rows with 0 values | Excel Programming | |||
Delete Rows where there are #N/A Values | Excel Worksheet Functions | |||
Delete rows with numeric values, leave rows with text | Excel Programming |