Macro Find & Replace problem

  • Thread starter Thread starter Craig Helm
  • Start date Start date
C

Craig Helm

I send off a spreadsheet to a central payroll office once a week that
has links to many other files.

One column has the occasional entry, the rest blank. Payroll demands
that it have nothing but the relevant data (ie there is no links in
the column when they receive it.)

I recorded a macro that copies then pastes the values just prior to
sending. That got rid of the links and kept the appropriate values.
Unfortunately it leaves a 0 in the cells that is only visible in the
formula bar. This also causes problems for them.

I thought I had a brainwave - Find & Replace 0 with "". Worked a
treat, until I realised that it also stripped 0's from 10's, making
them a 1 !

My next plan would be to write something that said:
IF less than 1 then delete, otherwise leave value in cell.

Although familiar with recording macro's, and picking out bits from
other's macro's I have little experience at writing macro's from
scratch. I'd appreciate assistance, even if there is a better way of
eliminating the zero's without a macro I'd be interested in seeing how
such an IF statement should be written.

And no, it does not seem related to the Tools-Options setting of Zero
Values.

Thanks,
Craig
 
You simply need to replace 0's with blanks IF THE ENTIRE CONTENTS of the
cell is 0:
Sub RepZero()
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole
End Sub

Bob Umlas
Excel MVP
 
Back
Top