Find in column range only

[rmwilson]

Can this range be modified to columns B to IV?

Set numrange = ActiveSheet.UsedRange

Thanks!

 

[schielrn]

Yes, try:

Set numrange = ActiveSheet.range("B:IV")

Hope that helps.

 

[rmwilson]

schielrn,

Thanks for the reply. Seems to work fine, but takes a while to complete.

rmw

 

[schielrn]

Well thats because it is going through 255 columns by 65536 rows, this is assuming you are using Excel 2003, which would equate to 16,711,680 cells?

Maybe you want to modify the code so it doesn't go out to the last column?

Maybe just use:

lastColumn = cells(1,columns.Count).end(xltoleft).address
Set numrange = ActiveSheet.range("B1:" & Mid(lastColumn, InStr(lastColumn, "$") + 1, InStr(2, lastColumn, "$") - 2)

This limits the amount of columns looked in, but you may also want to limit the rows. That is where used range was nice. I guess you could possibly also do this:

Set numrange = activesheet.usedrange.offset(0,1).resize(,activesheet.usedrange.columns.count-1).address

Hope that helps.