Macro for blanksSolved/Closed

Question

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"

Configuration: Windows 7 / Mozilla 11.0

vcoolio · Answer

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.

Macro for blanks

1 reply

Subscribe To Our Newsletter!