VBA highlight and count empty cells in a range of data

canarycat123

New Member
Joined
Sep 1, 2021
Messages
27
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi there - beginner to VBA here, so what I'm about to ask is way out of knowledge base, but am wondering if someone can help.

I would like code that is able to do the following:

1. Within a range of data highlight empty cells in yellow up until the last populated row. That will need to be dynamic as rows will continue to be added, and any empty cells there should also be highlighted.
2. Count the number empty cells within that range and display the number on another sheet.
3. When an empty cell is filled the highlight should be removed and the count reduced on the other sheet.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Does that need to be in VBA? That looks like it can be accomplished with a simple conditional formatting rule (for items 1 and 3) and the COUNTBLANK formula (for item 2).
 
Upvote 0
Does that need to be in VBA? That looks like it can be accomplished with a simple conditional formatting rule (for items 1 and 3) and the COUNTBLANK formula (for item 2).

Thanks - I was overcomplicating the highlight element and have now implemented that solution. In terms of the COUNTBLANK formula, is there a way of making that dynamic to capture cells in the row beneath when new data is added?

For example, in column A I have reference numbers. In columns C-G I have data associated with each unique reference. Currently that data covers C9:G67. When a new reference is added to A68 can the COUNTBLANK formula then adapt to capture any blank cells in C68:G68? Then so on and so forth as new references are added to column A?

Sorry if the answer is obvious, but I'm very new to combining Excel formula's, VBA etc.
 
Upvote 0
As in the example below, you can prepopulate a cell with a formula that calculates on certain conditions. So for example, the formula in column I will count how many blanks are in the cell range Column C to G in it's row. I've put that formula in every cell of Column I until Row 10, and so you will see that from Row 4 onwards, it starts saying 5 blank cells (even though there is no entry). In Column J, I've adjusted the formula so that if there is no unique reference number in column A, then it doesn't calculate anything. The moment you put any content into Column A, it will automatically calculate the number of blank cells.

Book1
ABCDEFGHIJ
1ReferenceDateFirstNameLastNameAgeLocationOccupationBlankCellsSmartBlankCells
2A00115/09/2021HattoriHanzoJapanSword Maker11
3A00216/09/2021Potter15UK22
4A00317/09/2021AliceWonderlandTresspasser22
55 
65 
75 
85 
95 
105 
Sheet1
Cell Formulas
RangeFormula
I2:I10I2=COUNTBLANK(C2:G2)
J2:J10J2=IF(A2<>"",COUNTBLANK(C2:G2),"")


Is that what you're after?
 
Upvote 0
Solution
As in the example below, you can prepopulate a cell with a formula that calculates on certain conditions. So for example, the formula in column I will count how many blanks are in the cell range Column C to G in it's row. I've put that formula in every cell of Column I until Row 10, and so you will see that from Row 4 onwards, it starts saying 5 blank cells (even though there is no entry). In Column J, I've adjusted the formula so that if there is no unique reference number in column A, then it doesn't calculate anything. The moment you put any content into Column A, it will automatically calculate the number of blank cells.

Book1
ABCDEFGHIJ
1ReferenceDateFirstNameLastNameAgeLocationOccupationBlankCellsSmartBlankCells
2A00115/09/2021HattoriHanzoJapanSword Maker11
3A00216/09/2021Potter15UK22
4A00317/09/2021AliceWonderlandTresspasser22
55 
65 
75 
85 
95 
105 
Sheet1
Cell Formulas
RangeFormula
I2:I10I2=COUNTBLANK(C2:G2)
J2:J10J2=IF(A2<>"",COUNTBLANK(C2:G2),"")


Is that what you're after?

Thank you so much - that's exactly what I need! Works perfectly :)
 
Upvote 0
Glad to hear it. I'd be grateful if you could please mark it as the solution so that others will know that it was helpful.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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
Back
Top