I have an excel file that has 35 worksheets. I wanted to create a button that when clicked, it goes to the relevant worksheets and clears the specified content, not all the content. I've tried following a few different Youtubes and ChatGPT but they don't quite cover this scenario or give the right results. Some of the cells to be cleared are merged so I've since learnt that these need to be handled a special way as well. Here is the VBA code I have tried using but I keep getting a Run-time error 9 Subscript Out of Range. I have no idea how to find what the problem is and I have checked all the cell references about 80 times. haha.
Please look over the code I have and see if there is something missing from this code to make it work or if its totally wrong and what I need to write instead.
Please look over the code I have and see if there is something missing from this code to make it work or if its totally wrong and what I need to write instead.
VBA Code:
Sub ClearRanges()
Dim ranges(1 To 13) As Range
Dim ws As Worksheet
Dim i As Integer
' Set the ranges to the desired cells on each worksheet
Set ranges(1) = Worksheets("Sheet2").Range("B18:K50,P18:R50,AB18:AC50,AN18:AQ50,AT18:AT50,D11")
Set ranges(2) = Worksheets("Sheet4").Range("A2:AU2500")
Set ranges(3) = Worksheets("Sheet8").Range("C10:H21")
Set ranges(4) = Worksheets("Sheet11").Range("A2:BJ1379")
Set ranges(5) = Worksheets("Sheet14").Range("F22,I24:I25")
Set ranges(6) = Worksheets("Sheet15").Range("AG11:AI1368,AL11:AP1368")
Set ranges(7) = Union( _
Worksheets("Sheet16").Range("E17:K1379,AY17:AY1379"), _
Worksheets("Sheet16").Range("AY9:AY10"), _
Worksheets("Sheet16").Range("AZ9:AZ10"), _
Worksheets("Sheet16").Range("AZ3,c8,c9") _
)
Set ranges(8) = Worksheets("Sheet17").Range("B18:K50,P18:R50,AB18:AC50,AN18:AQ50,AT18:AT50,D11")
Set ranges(9) = Union( _
Worksheets("Sheet19").Range("C8:G8"), _
Worksheets("Sheet19").Range("C9:G9"), _
Worksheets("Sheet19").Range("C10:G10"), _
Worksheets("Sheet19").Range("C11:G11"), _
Worksheets("Sheet19").Range("C12:G12"), _
Worksheets("Sheet19").Range("E12,D14:D17,D19,F19,K14,K19,L15:L18,C35:C40,E35:E40,C51:C53,E51:E61,E71:E74,E85:E86,G85:G86,C98:C105,E98:E105,C125:C126,E125:E129,AD7:AE18,AG7:AM18,AT7:AU18,AW7:BB18,BF7:BG18,AJ39,AF48:AF50") _
)
Set ranges(10) = Worksheets("Sheet21").Range("C31:N31,C40:N40")
Set ranges(11) = Worksheets("Sheet24").Range("J3:U3,J7:U10,X11,X14,X16,J16:U16,J22,J23,J26,J29,J30,D42,F42")
Set ranges(12) = Worksheets("Sheet25").Range("M29:M31")
Set ranges(13) = Union( _
Worksheets("Sheet28").Range("C24:F24,I24,J24,M24,N24,P24"), _
Worksheets("Sheet28").Range("n9:o9"), _
Worksheets("Sheet28").Range("n10:o10"), _
Worksheets("Sheet28").Range("p9:q9"), _
Worksheets("Sheet28").Range("p10:q10"), _
Worksheets("Sheet28").Range("D49,I5") _
)
' List the relevant sheet names
Dim relevantSheets As Variant
relevantSheets = Array("Sheet2", "Sheet4", "Sheet8", "Sheet11", "Sheet14", "Sheet15", "Sheet16", "Sheet17", "Sheet19", "Sheet21", "Sheet24", "Sheet25", "Sheet28") ' Add the relevant sheet names here
' Loop through each relevant worksheet in the workbook
For Each ws In ThisWorkbook.Worksheets
If IsInArray(ws.Name, relevantSheets) Then
' Loop through the ranges and clear them on each relevant worksheet
For i = 1 To 13 ' Adjust the range index accordingly
ws.Range(ranges(i).Address).ClearContents
Next i
End If
Next ws
End Sub
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function