put your column numbers into another array in the same order as the

sheets:



Dim DeleNum As Range

Dim c As Range

Dim rngC As Range

Dim varSheets As Variant

Dim varCols As Variant

Dim DelC As Range

Dim i As Long

Dim Lrow As Long



With Sheets("Sheet1")

Lrow = .Cells(.Rows.Count, 1).End(xlUp).Row

Set DeleNum = .Range("A1:A" & Lrow)

End With



varSheets = Array("Sheet2", "Sheet3", "Sheet4")

varCols = Array(15, 12, 1)



For i = LBound(varSheets) To UBound(varSheets)

With Sheets(varSheets(i))

For Each c In DeleNum

.Columns(varCols(i)).Replace What:=c, Replacement:="", _

LookAt:=xlPart, SearchOrder:=xlByRows, _

MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Next

End With

Next



End Sub




Regards

Claus B.


Thanks Claus. Once written it seems quite straight forward and understandable. Alas, I would have never gotten there on my own.
Google failed to turn up an example, where I spent about an 45 min looking.

With a silent count it cuts the code down to about 12 to 14 seconds. Previous was maybe 20 seconds. I assume that's a small price to pay when one column is 350 rows and two are about 1200 to 1500 rows. I assume it goes all the way down in each of the column to 1 million + rows.

Not sure its worth it to try to row specific in the search range. A column may have 40 number entries then a single "Title Cell" then maybe all blanks until the next "Title Cell" and numbers and blanks and Titles until the end of that columns data.

At any rate, thanks for all the help.