ctrl+F?

[xtscars]

I would like to duplicate the find and replace function in a macro. I want to find all the zeros in my final column, and replace them with nothing, so they'll be blank.

I have my final column defined as: finalcolumn=range("xfd1").end(xltoleft).column

is there any code that will do this for me?

 

[jvanbonn]

Try This...

Sub replaceall()
'
' replaceall Macro
'

'
Dim finalcolumn As Long
Dim MyRange As Range

finalcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Set MyRange = ActiveSheet.Range(Cells(1, finalcolumn), Cells(ActiveSheet.Rows.Count, finalcolumn))

MyRange.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

 

[xtscars]

I'm trying to use that, but it's saying there is a "Compile error: Invalid character" and it highlights that last "_" between "False," and "ReplaceFormat"...do you have any advice on this?

Thanks for the the help!

 

[jvanbonn]

I deleted the following. It wasn't necessary.

                                [COLOR=Red][B], _[/B]
[B]        ReplaceFormat:=False[/B][/COLOR]

Try this...

Sub replaceall()
'
' replaceall Macro
'

'
Dim finalcolumn As Long
Dim MyRange As Range

finalcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Set MyRange = ActiveSheet.Range(Cells(1, finalcolumn), Cells(ActiveSheet.Rows.Count, finalcolumn))

MyRange.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False
End Sub