Search code examples
excelreplacevbscript

Find and replace in specific Excel column

How to modify this script to only find and replace in a specific column?

Set xl = CreateObject("Excel.Application")
    xl.Visible = True
Set wb = xl.Workbooks.Open("C:\Users\test.xlsx")
Set ws = wb.Sheets("Sheet1")

Set objRange = ws.Range("Q1").End(xlDown).Select
objRange.Replace "~*", ""

The current script gives an error on the Set objRange line. Replacing * in the entire sheet would interefere with formulas.

Solution

  • You're thinking too complicated. Just tell the Range property that you want the entire Q column and call the Replace method directly on that Range object:

    ws.Range("Q:Q").Replace "~*", ""