Macro Fill Empty Cells

Hi,

I have created the below Macro to fill empty blank cells;

Sub TEST()
Dim c As Range
For Each c In ActiveSheet.Range("D3:D20000")
If c = vbNullString Then c = "VNA"
Next
End Sub

However the issue I am having its continuing right down the excel sheet, I need it to stop on the last line that contains text because its affecting the pivot table results.

Hi
your macro is continuing to cell D2000 because that is the range you have told it to fill.
To fill only blank cells to the last used row in the active sheet, try this :

Please Login or Register  to view this content.

Much better thanks, however it stilled filled past where my text ends, not the whole sheet now but down to cell 1000, is there a bit of data to put in to stop it after the last line of text.

Hi
The macro should only work to the row of the last used row on the worksheet (not necessarily in column D). You might want to check if there is any content below your main table in the sheet.

Or, if you only want to go to the last row in column D, try this:

Please Login or Register  to view this content.