Replace all numbers with zero, except formulas

Hansulet

Board Regular
Joined
Jan 24, 2013
Messages
97
I have a specified range selected.

Range(ActiveCell.Offset(0, -9), ActiveCell.Offset(0, -3)).EntireColumn.Select

Now, I have to replace all numbers with zero in the above range, except formulas.

Can anyone help me?
 

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
565
Try

Selection.SpecialCells(xlCellTypeConstants, 1).Select
Selection.FormulaR1C1 = "0"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
How about
Code:
ActiveCell.Offset(, -9).Resize(, 7).EntireColumn.SpecialCells(xlConstants, xlNumbers).Value = 0
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback.

For reference, it's always best to avoid selecting things, as it's normally not needed & slows down the code.
 

Forum statistics

Threads
1,078,148
Messages
5,338,522
Members
399,240
Latest member
mominul2241

Some videos you may like

This Week's Hot Topics

Top