Macro for blanks
Solved/Closed
Connie
-
Apr 17, 2015 at 09:36 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Apr 21, 2015 at 06:37 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 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"
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"
Related:
- Macro for blanks
- Spell number in excel without macro - Guide
- Excel macro download - Download - Spreadsheets
- Online macro excel - Guide
- Excel run macro on open - Guide
- Excel macro to create new sheet based on value in cells - Guide
1 response
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Apr 19, 2015 at 07:21 AM
Apr 19, 2015 at 07:21 AM
Hello Connie,
Replace this part of your code:-
with this:-
to see if it helps.
The code assumes that you have a heading in W1.
Cheerio,
vcoolio.
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.
Apr 21, 2015 at 08:05 AM
Apr 21, 2015 at 06:37 PM
Cheerio,
vcoolio.