Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to clearcontents of "0" values in cells
This should be simple, but I'm just not getting it. There are only two
ranges I care about Range("J:J") and Range("K:K"). There are many numeric values in the cells in these columns, but I just want to run a macro to delete all the "0" values. I've muddled in this, and the closest I could get it tested the value with an if statement and then, when it found the range true to have a "0" within the column, it cleared the contents of the whole column. Please help! I just need to delete the cells that have zeros in them so I can run average totals at the bottom of the columns. Obviously, I don't want to average in the zeros! Thanks, LeeCB ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to clearcontents of "0" values in cells
You could cycle the range like this:
For i = 0 to 1 if i = 0 then Set SearchRange = ActiveSheet.Range("J1:J" & ActiveSheet.Range("J65536").End(xlup).Row) else Set SearchRange = ActiveSheet.Range("K1:K" & ActiveSheet.Range("K65536").End(xlup).Row) End If For Each Cell in SearchRange If Cell.Value = 0 then Cell.ClearContents Next Cell Next i tod -----Original Message----- This should be simple, but I'm just not getting it. There are only two ranges I care about Range("J:J") and Range("K:K"). There are many numeric values in the cells in these columns, but I just want to run a macro to delete all the "0" values. I've muddled in this, and the closest I could get it tested the value with an if statement and then, when it found the range true to have a "0" within the column, it cleared the contents of the whole column. Please help! I just need to delete the cells that have zeros in them so I can run average totals at the bottom of the columns. Obviously, I don't want to average in the zeros! Thanks, LeeCB ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheets("Sheet1").Cells.ClearContents | Excel Discussion (Misc queries) | |||
"Resizing cells" code doesn't work with merged cells | Excel Discussion (Misc queries) | |||
VLookup returning the sum of multiple values from one "code" | Excel Worksheet Functions | |||
"ClearContents" command halts code | Excel Discussion (Misc queries) | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |