Automatically display text on empty cells

scarletX

New Member
Joined
Mar 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi there, I'm relatively new to VBA in Excel and I stumbled across this forum in hopes that my problems can be solved.

It might be an easy task for VBA experts, so I just wanna ask as the thread title stated;

How do I automatically display text in a range of cells (multiple columns) when the cell(s) are empty, and even after I entered value inside and then deleted it, the cell will automatically display the same text that I set for the rest of the cells?

I tried the If Then function, but it only works on one cell and does not automatically display when I typed a value and deleted it.

I tried Googling but the codes that were given were something I don't understand, which I can't work it out in the end. I need help and I would also appreciate if someone explain to me on their solution in layman terms as I am trying to learn VBA along the way as well.

Thanks in advance
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Can you walk us through an actul example? Please include specific range references.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

scarletX

New Member
Joined
Mar 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Can you walk us through an actul example? Please include specific range references.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Hi Joe, thank you for the warm introduction.

However, I do not have my Excel and file with me right now. But I can remember what I needed.

From cells C5 to C15, F5 to F15, J5 to J15 it will be blank. However, I don't want it to appear as empty cells before anyone inputs any values.
So from what I had was

VBA Code:
If Range("C5:C15","F5:F15","J5:J15") = "" Then
Range("C5:C15","F5:F15","J5:J15") = "Enter Text Here"

I get error whenever I run this, so I can only do one cell for the If portion, something like

VBA Code:
If Range("C5") = "" Then
Range("C5:C15","F5:F15","J5:J15") = "Enter Text Here"

Unless I do hard coding which will be very inefficient to apply If and Then function for 33 times
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
You cannot do a whole range at once like that. A whole range is either empty, or it is not.
If you want to check each cell, you will need to loop through the range, cell-by-cell, like this:
VBA Code:
    Dim rng As Range
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
    Set rng = Range("C5:C15, F5:F15, J5:J15")
    
    For Each cell In rng
        If cell = "" Then cell = "Enter Text Here"
    Next cell
    
    Application.ScreenUpdating = True
 
Solution

scarletX

New Member
Joined
Mar 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You cannot do a whole range at once like that. A whole range is either empty, or it is not.
If you want to check each cell, you will need to loop through the range, cell-by-cell, like this:
VBA Code:
    Dim rng As Range
    Dim cell As Range
   
    Application.ScreenUpdating = False
   
    Set rng = Range("C5:C15, F5:F15, J5:J15")
   
    For Each cell In rng
        If cell = "" Then cell = "Enter Text Here"
    Next cell
   
    Application.ScreenUpdating = True
I see, thanks for the answer, I will try it later when I get back to my file.

Since we are at this topic, don't mind me asking, what does the
VBA Code:
Application.ScreenUpdating
do?
And everytime I see people writing
VBA Code:
dim rng As Range
, for the
VBA Code:
rng
I can set this to any name? Or it has to be rng ?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
Since we are at this topic, don't mind me asking, what does the
VBA Code:
Application.ScreenUpdating
do?
What that does is temporarily suppress the screen updating while the code is running.
So instead of updating the screen image, one at a time, for each cell update, it waits until the code is finished, and posts/shows all the cell updates at once.
It reduces the "screen flickering" you sometimes see when your code is running, and makes the code run faster too.
This is especially noticeable when your code is making a bunch of updates to the worksheet, especially if there are loops involved.

I can set this to any name? Or it has to be rng ?
You can use any variable name that you like. Just avoid using reserved words, like the names of existing functions, objects, properties, etc.
 

scarletX

New Member
Joined
Mar 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What that does is temporarily suppress the screen updating while the code is running.
So instead of updating the screen image, one at a time, for each cell update, it waits until the code is finished, and posts/shows all the cell updates at once.
It reduces the "screen flickering" you sometimes see when your code is running, and makes the code run faster too.
This is especially noticeable when your code is making a bunch of updates to the worksheet, especially if there are loops involved.


You can use any variable name that you like. Just avoid using reserved words, like the names of existing functions, objects, properties, etc.
Thank you

However, the codes didn't work, it says
Compile error:
Wrong number of arguments or invalid property assignment

And they highlighted on the "Range" from
rng = Range("C5:15"....

Also, do I have to input the script on a new module? Or it can be done on ThisWorkbook?
 

scarletX

New Member
Joined
Mar 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Wait, I know what was the issue now, I've been inputting the range of cells in the wrong way.
Should've been
VBA Code:
Range("C5:C15,F5:F15,J5:J15")
But I input it as
VBA Code:
Range("C5:C15","F5:F15","J5:J15")
Silly me..

Thank you so much, now it works!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,395
Messages
5,636,057
Members
416,895
Latest member
SteveRandall

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