Previous Topic Next Topic

Macro for blanks

[Solved/Closed]
Report
-
Posts
1318
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 20, 2021
 -
Hello,
Would appreciate help!
I have a spreadsheet which I run daily. The number of rows can change each time I run it (ie one day I have 5 rows; the next day I may have 30).
Within this one page spreadsheet, Col W contains cells that can have data or blanks. Or sometimes all the cells in Col W are filled with data. When there are blanks, I need to replace the blanks with text (I need "'6/30/2014" to appear as text not a date). As part of a larger macro I've created, I highlight Col W and use the GoTo-blanks and replace them with my "'6/30/2014" data.
My problem is if Col W has no blanks-every cell being filled - I get an error on the macro. How do I correct that situation?

Columns("W:W").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "'6/30/2014"

1 reply

Answer 1 / 1
Posts
1318
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 20, 2021
 238
Hello Connie,

Replace this part of your code:- 

Columns("W:W").Select 
 Selection.SpecialCells(xlCellTypeBlanks).Select 
 Selection.FormulaR1C1 = "'6/30/2014"


with this:- 


On Error Resume Next
For Each c In ActiveSheet.Range("W2:W" & Rows.Count).SpecialCells(xlCellTypeBlanks)
If c.Value = "" Then
 c.Value = "'6/30/2014"
 End If
Next


to see if it helps.

The code assumes that you have a heading in W1.

Cheerio,
vcoolio.
Report

This worked great! thanks
Report
Posts
1318
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 20, 2021
 238 > Connie
Glad I could help, Connie.

Cheerio,
vcoolio.
Ask a question