Fill in blanks with N/A in the next row

[quadaxel]

My code below helps me to fill in blank cells with N/A from range A18:Y18. However, I'm not sure how to run the same code for the next rows (ie A19:Y19, A20:Y20).

My usual process is to enter the raw data into row 18 first, then run this VBA code to fill in blank cells with N/A. Next, I will enter another set of data into row 19, and fill in the blanks with N/A. I want to repeat this process for each row. How should I edit my code?

Sub FillEmptyCells()
'Forces each cell to show N/A if Empty
On Error Resume Next
Dim c As Range
Dim rng As Range
Set rng = Range("A18:Y18")
    For Each c In rng
If IsEmpty(c) Then
c = "N/A"
End If
Next c
End Sub

 

[Trevor G]

Welcome to the Forum, would this work for you.

Sub checkCell()
Range("A18").CurrentRegion.SpecialCells(xlBlanks).Value = "n/a"
End Sub

 

[quadaxel]

Thank you for your help. However, the code above helps to fill in N/A into the blank cells. Is there any way to help me to fill in N/A into the next row's blank cells, without me having to edit the range?

 

[Trevor G]

The CurrentRegion will extend down automatically when you add more data, each time you run the code it will fill in N/A in any empty cell.

 

[quadaxel]

Oh I see...

I ran into another issue when I run the code. My rows above row 18 are affected. Those rows already contained data, but how did those data get replaced with N/A as well?

The CurrentRegion will extend down automatically when you add more data, each time you run the code it will fill in N/A in any empty cell.