How do I Fix Subscript out of range Error when Using Range object with Thousands of Rows?

[Shullay]

Hello Everyone I was working on trying to write a routine that would redact sensitive information in reports that that would have varying degrees of rows and columns. It uses a collection as a master list of qualifiable columns to redact and searches for them in the report. If a match is found it then finds the column and address. Creates a string range address for that column which is used in the line

Please Login or Register  to view this content.

However, I get a Subscript out of range runtime error for the For Each line above. I was wondering what is causing this when I have thousands of rows to redact butit will work when there are only a few rows to redact.

Please Login or Register  to view this content.

[kalak]

On a quick look it seems your worksheet names may be somewhat confused.

Before the 3 lines add: Sheets("Sheet1").Activate
or alternatively
For Each Cell In ActiveSheet.Range(sTempRange)
...

and see what happens.

Have you verified that in fact there is a range named Range(sTempRange) on Sheet1?