blank cells if loop

[dshafique]

Hi guys, im trying to fill all the blank cells in a selected range with the text "no data". however, im doing a monthly report, and its not necessary for each month to have blank cells. so how can I make an if loop where it checks if blank cells exist in the range, if they do, fill with "no data" otherwise, dont do anything. i have this for filling in blank cells


ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = "Null"

 

[jim may]

Try instead

ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = "no data"

Looping is not needed as this one-liner does it all at the same time.

 

[dshafique]

So i was looking for an if statement because if there's no blank cells, it gives an error. these are sheets i produce monthly, so sometimes there are no blank cells. basically

if blank cells in range
then fill with "No Data"
else do nothing

 

[Fluff]

Try this mod on Jim's code

On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = "no data"
On Error Goto 0

 

[jim may]

Right on ... Fluff. Tks for jumping in.. Jim

 

[dshafique]

I made a loop, thanks!