Replacing VbTab with Underscore in ActiveSheet

[Rasm]

I have the following code - But I get runtime error 13 - type mismatch.

What I am trying to do is replace any VbTab character in any cell with an underscore in my ActivSheet. The code can be completely different from my approach - it does not work anyway.



Private Sub ReplaceVbTabWithUnderscore()
'Makes sure that the individual cells dont contain a VbTab
'VbTab is used as delimiter for keys in the TreeView
'Any VbTab in a cell is replaced with an _ (underscore)
Dim RangeVal As String
With ActiveSheet.UsedRange
LastRow = .Rows(.Rows.Count).Row
End With
With ActiveSheet.UsedRange
ColLast = .Columns(.Columns.Count).Column
End With
RangeVal = "A1:" & Split(Cells(1, ColLast).Address, "$")(1) & LastRow
ActiveSheet.Range(RangeVal) = Replace(ActiveSheet.Range(RangeVal), vbTab, "_")
End Sub

[Excel Fox]

Replace the last line with


Call ActiveSheet.Range(RangeVal).Replace(vbTab, "_")

[Admin]

Hi Rasm,

This works for me.


activesheet.usedrange.Replace chr(9),"_",2

Kris

[Rasm]

You mean replace entire code with that one line - sweeetttt - it works - tyvm:D