N/A blank cells in Sheet where data is last entered

quadaxel

New Member
Joined
Jul 25, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have a workbook, consisting for 5 sheets (110, 220, 100, 200, 400). I have created a code to compile information automatically into the respective sheet according to their category. For instance, I have this information for sheet 110 and I run the code to compile the information into that particular sheet. This information goes into row 18. Next, I need to N/A all the blank cells in that row in sheet 110. Following that, I have another set of information to be entered into sheet 110, into the next row, which is sheet 19. After that, N/A all blank cells before moving on to key in information into the next row.

What code should I write for to get my N/A included in next row, in that particular sheet?

VBA Code:
Sub FillEmptyCells()
'Forces each cell to show N/A if Empty
On Error Resume Next
Dim lastrow As Long
Range("A18").CurrentRegion.SpecialCells(xlBlanks).Value = "N/A"
End Sub

This is my current code for N/A. But this code affect my previous rows (ie, my headers got N/A as well). Can I have some help here too?

Thank you.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,301
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
With ActiveSheet
   On Error Resume Next
   Intersect(.UsedRange, .Rows("18:19")).SpecialCells(xlBlanks).Value = "N/A"
   On Error GoTo 0
End With
 

quadaxel

New Member
Joined
Jul 25, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Thank you for your help!

Is there another way to N/A cells in the next row, without specifying which row #? First, I will fill information into row 18. Then, N/A any cells that are not filled. Next, I will fill information into row 19. Then N/A any cells that are not filled. These two steps will be repeated, when necessary.

I also have another issue, why is it that my headers get N/A as well? Could it be due to the spaces in between?

I've attached a screenshot excel sheet for clearer information.
Annotation 2020-08-04 145941.jpg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,301
Office Version
  1. 365
Platform
  1. Windows
why is it that my headers get N/A as well?
Probably because you are using merged cells, which are an abomination & should be avoided like the plague.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,317
Messages
5,600,915
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top