CODEWITHSUNDEEP
excelvbarange
Rich Prag
Rich Prag

Reputation: 133

VBA to select only certain sheets within a workbook to then clearcontents from a range

I'm trying to write some code that selects certain sheets (not all, and the sheets aren't all next to each other either), clears the same range of cells from each, unselects them, selects another group of sheets, clears the same range from each, etc. Here's the code. 

ActiveWorkbook.Sheets(Array("J2a", "J7", "J10", "J11", "J13", "J17", "J18", "J19")).Select
    Range("C12:E14, C22:E24, C32:E34, C42:E44, C52:E54, C62:E64, C72:E74,C82:E84, C92:E94, C102:E104, C112:E114, C122:E124, C132:E134, C142:E144, C152:E154").ClearContents
        ThisWorkbook.Worksheets("Control").Activate

The problem is that it only clears the contents from the active sheet (which is the first one in the select list). So I changed the code to the following as I thought there must be a way to clear contents on multiple sheets without selecting the sheets: 

ActiveWorkbook.Sheets(Array("J2a", "J2b", "J7", "J10", "J11", "J13 DM", "J13 DS", "J17", "J18", "J19")).Range("C12:E14, C22:E24, C32:E34, C42:E44, C52:E54, C62:E64, C72:E74, C82:E84, C92:E94, C102:E104, C112:E114, C122:E124, C132:E134, C142:E144, C152:E154").ClearContents

However now I get an error that says 'Object doesn't support this property or method.

What am I doing wrong? the two lines seem to work independently...Also for my own understanding, what is the object in the error? ActiveWorkbook?

Upvotes: 0

Views: 1746

Answers (1)

Teasel
Teasel

Reputation: 1340

You can use something like the code below. This will go through all the sheets that have their name in the array sheetNamesArray and clear every range that you defined in rangesArray.

If you want to clear other sheets with other ranges, I suggest that you repeat this operation with other values.

Note: This assume that all of your sheets are in the same Workbook. 

Dim sheetNamesArray
Dim rangesArray

'Array with the names of all of your sheets
sheetNamesArray = Array("Sheet1", "Sheet2", "Sheet3")
'Array with all of your ranges
rangesArray = Array("A2:B2", "A4:B4", "A6:B6")

'Go through each sheet in the array
For Each sheetName In sheetNamesArray
    'Go to each range in the sheet
    For Each rangeName In rangesArray
        'Clear the value
        ThisWorkbook.Sheets(sheetName).Range(rangeName).Clear
    Next rangeName
Next sheetName

Upvotes: 1

Related Questions