Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear if "#N/A" and Find End of Range, Fill Blanks
Simple problem seems to be turning into a complex problem. I want to clear
all cells that show #N/A. Sub ClearRange() Dim r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "#N/A" Then rr.Offset(0, 1).Clear End If Next End Sub As an aside, I was trying to fill all blank cells with values from above cells. It is easy to find the end of the range, but Excel seems to lose the address of the last cell in the used range once I tell it to go to the first cell in the used range (in my case it is A5) Sub FindEnd() Dim Rng As Range Cells(Rows.Count, "A").End(xlUp).Select ActiveCell.Select Rng = ActiveCell Selection.SpecialCells(xlCellTypeBlanks).Select Range("A5:Rng").Select Selection.FormulaR1C1 = "=R[-1]C" End Sub Any help with either macro would be greatly appreciated!! Regards, Ryan--- -- RyGuy |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shortcut to switch from "fill down" to "copy" with mouse drag | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
"Invalid property" after "Clear Form" | Excel Programming | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming |