Macro for blanks

Solved/Closed
Connie - Apr 17, 2015 at 09:36 AM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Apr 21, 2015 at 06:37 PM
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 response

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Apr 19, 2015 at 07:21 AM
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.
This worked great! thanks
0
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262 > Connie
Apr 21, 2015 at 06:37 PM
Glad I could help, Connie.

Cheerio,
vcoolio.
0