Hi All,
I'm trying to sanitise some data and basically need to delete all 0s and "n/a" from a column. However, I know that if the cell is either of those values, I don't need the entire row.
So essentially the data I need to search is in Column "AD", select the cells that have "n/a" or 0 in them and delete the whole row.
Can anyone help? I've been doing it manually to select the cells I need, but when I do my traditional 'record macro & edit', it doesn't seem to come back with anything.
Thanks!
Hi,
This is all what I can do. you may need to run the code multiple times, though (I don't know why)
Change (change this value) to the maximum number of row in your worksheet (that you want to check the 0 or "n/a" values)
Example:
If you have 100 rows, so it'll become
For x = 1 To 100 Step 1
If you don't mind, just go ahead and put 9999
Hope this works.Sub Command1() 'Column AD is column #30 For x = 1 To (change this value) Step 1 If Cells(x, 30).FormulaR1C1 = "0" Or _ Cells(x, 30).FormulaR1C1 = "n/a" Then Rows(x).Delete Shift:=xlUp End If Next x End Sub
Last edited by 4Ferdinand; 06-21-2011 at 06:13 AM.
Hi 4Ferdinand,
Thanks, that's what I'm after, but it doesn't delete all of the zeros or the "n/a"s, even after I run it multiple times. I'll have a play and see what happens!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks