Clear data from multiple cells in multiple sheets.

[dnaragon]

Hi,

I want to create a "Clear All" button but I am having trouble with a macro to clear the same cell ranges from multiple sheets? Each sheet is named after each employee. Also, I don’t want to clear the first sheet called “Call History” and the cell range is F2:O3000. Can anyone help? Thank you for the help!

[hrlngrv]

If you had 5 worksheets named Call History, Bob, Carol, Ted, Alice, and you wanted to clear cell B3, C5, D7, and E9 in the last 4 in a single operation,

Worksheets(Array("Bob", "Carol", "Ted", "Alice")).Range("B3,C5,D7,E9").ClearContents

[dnaragon]

Thank you!

This didn't clear the cell range.

[hrlngrv]

Sorry. Working from evidently faulty memory and didn't test.

Apparently this is one of the few things for which it's necessary to select objects.

Please Login or Register  to view this content.

[jolivanes]

Or without selecting but with looping.

Please Login or Register  to view this content.

[dnaragon]

Hi,

Thank you hrlngrv and jolivanes! I used both but got the same error: Subscript out of range. Is there anything you see below that is incorrect?

Sub Maybe_Without_Selecting()
Dim wsArr, rngArr, i As Long
wsArr = Array(“Pete LASTNAME”, “Austin”, “Enzo”, “Caroline”, “Riley”, “Carey”, “Cassady”, “Alyssa”, “Nate”, “Nathan”, “Emily”, “Marissa”, “Chuck”, “Meghan”, “Kyndra”, “Jessica”, “Libby”, “Hanani”, “Brian”, “Dean”, “Ashley”, “Stephanie”, “Alyssa”, “Alec ”, “Hava”, “Katie”, “John”, “Greg”, “Jared”, “Dee”)
rngArr = Array(“F2:F5000”, “G2:G5000”, “H2:H5000”, “I2:I5000”, “J2:J5000”, “K2:K5000”, “L2:L5000”, “M2:M5000”, “N2:N5000”, “O2:O5000”)
For i = LBound(wsArr) To UBound(wsArr)
With Sheets(wsArr(i))
.Range(rngArr(i)).ClearContents
End With
Next i
End Sub

[jolivanes]

You have a space in "Alec "
That code needs an equal amount of entries in both arrays otherwise it fails. This one not

Please Login or Register  to view this content.

This would be a better approach by the looks of it.

Please Login or Register  to view this content.

[dnaragon]

This worked, thank you for all you help!!!

[jolivanes]

Thanks for letting us know and Good Luck