Find & replace up to certain row

[srosk]

Is there a way for this code to only look until the last populated row in column A vs the entire columns D:K. IE, the data stops on row 84, but the following populates ' N/A' all the way down D:K where a cell is blank.

Thanks in advance!

    Columns("D:K").Select    Selection.Replace What:="", Replacement:="       N/A", lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[Fluff]

Are those cells actually blank, or do they contain formulae that return ""

 

[srosk]

They're blank cells that it is replacing with ' N/A'

 

[Fluff]

In that case, try

Sub srosk()
Range("D:K").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
End Sub

 

[srosk]

Thanks.. can check soon. So if cell A86 has a value and D is blank, " N/A" will appear in D86.

If A87 is blank, will D87 still be updated to " N/A" ? Ideally, it would remain blank (if possible). Not sure that logic is built in? Thanks again, as allways, Fluff!

 

[Fluff]

That will fill any blank cells in the used range for cols D:K with N/A

 

[srosk]

Is there a way for it to determine the row to stop on.. IE: stop at row 86 since row 87 onward is not used?

 

[Fluff]

It only acts on the UsedRange, so if row 86 is the last row in the used range, that is where it will stop.

 

[srosk]

Ok, my bad. I see what's happening. I copy 395 rows, do vlookups, and delete all rows where the vlookup result is #N/A, using a autofilter command. I think the UsedRange formula is still reading the blank rows 85-395 for some reason. I will try and figure out how to truly delete them.

Another issue that I am having, is that it does not actually fill in the columns where a row has data. For instance... J6 is blank. E6:I6 have values. When I run your formula, " N/A" does not populate in J6. Any idea about this one? I tried the following, but it didn't yield different results.

Range("D:D").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
Range("E:E").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
Range("F:F").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
Range("G:G").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
Range("H:H").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
Range("I:I").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
Range("J:J").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"
Range("K:K").EntireColumn.SpecialCells(xlBlanks).Value = "       N/A"

Thanks as always!

 

[srosk]

So I think I got it... this fills in the N/A value, then deletes everything below where column A is blank

    Columns("D:K").Select    Selection.Replace What:="", Replacement:="       N/A", lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
   Columns("A:A").Select
   Selection.SpecialCells(xlCellTypeBlanks).Select
   Selection.EntireRow.Delete