Clear content in multiple ranges on 14 different worksheets.

M

MAP77

Board Regular
Joined
Sep 19, 2018
Messages
60
Office Version
  1. 365
Platform
  1. Windows
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. :)

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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
I Think your problem is in the isinarray function. I don't have the filter function in my version of Excel but I rewrote it using VBa and the code runs without any problem. I think my version of the isinarray does the same as yours (I have not experience of using filter!!) So try this function and see if it solves your problem:
VBA Code: 
Function isinarray(ws1, rs)
isinarray = False
For i = 0 To UBound(rs)
 If ws1 = rs(i) Then
  isinarray = True
  Exit For
 End If
Next i
End Function
 
Upvote 0
MAP77 said:
but I keep getting a Run-time error 9 Subscript Out of Range
Click to expand...
Runtime errors are specific to a particular line of code, so it is important to include which line of code causes the problem
 
Upvote 0
offthelip said:
I Think your problem is in the isinarray function. I don't have the filter function in my version of Excel but I rewrote it using VBa and the code runs without any problem. I think my version of the isinarray does the same as yours (I have not experience of using filter!!) So try this function and see if it solves your problem:
VBA Code: 
Function isinarray(ws1, rs)
isinarray = False
For i = 0 To UBound(rs)
 If ws1 = rs(i) Then
  isinarray = True
  Exit For
 End If
Next i
End Function
Click to expand...
Hi, I applied the change but I'm still getting the RTE 9.
 
Upvote 0
rlv01 said:
Runtime errors are specific to a particular line of code, so it is important to include which line of code causes the problem
Click to expand...
How do you determine which line of code is the problem? I hit debug and it highlights the first range set. I deleted the first range set and now when I hit debug it highlights the new first range in the set.
 

Attachments

  • Screenshot 2023-05-23 145043.png
    Screenshot 2023-05-23 145043.png
    245.3 KB · Views: 13
Upvote 0
MAP77 said:
How do you determine which line of code is the problem? I hit debug and it highlights the first range set. I deleted the first range set and now when I hit debug it highlights the new first range in the set.
Click to expand...
MAP77 said:
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. :)

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
Click to expand...
UPDATE ON MY ISSUE:

Ok, so I've since learned that you have to put the name of each worksheet in the code, not the sheet referenced number. 🤦‍♀️.
So after I corrected this and ran the code again, I now get a RTE 1004 - We can't do that to a merged cell. When I hit debug, here is the line that gets highlighted. Any ideas?
 

Attachments

  • Screenshot 2023-05-23 154537.png
    Screenshot 2023-05-23 154537.png
    193.8 KB · Views: 11
Upvote 0
If you do it another way, do you get the same kinds of error messages? For example
(not tested)
VBA Code: 
Sub ClearRanges1()
    Dim rng As Range
    Dim WS As Worksheet

    ' Loop through each relevant worksheet in the workbook and clear the ranges.
    For Each WS In ThisWorkbook.Worksheets
        Set rng = Nothing
        With WS
            Select Case .Name
            Case "Sheet2"
                Set rng = .Range("B18:K50,P18:R50,AB18:AC50,AN18:AQ50,AT18:AT50,D11")
            Case "Sheet4"
                Set rng = .Range("A2:AU2500")
            Case "Sheet8"
                Set rng = .Range("C10:H21")
            Case "Sheet11"
                Set rng = .Range("A2:BJ1379")
            Case "Sheet14"
                Set rng = .Range("F22,I24:I25")
            Case "Sheet15"
                Set rng = .Range("AG11:AI1368,AL11:AP1368")
            Case "Sheet16"
                Set rng = Union(.Range("E17:K1379,AY17:AY1379"), .Range("AY9:AY10"), .Range("AZ9:AZ10"), .Range("AZ3,c8,c9"))
            Case "Sheet17"
                Set rng = .Range("B18:K50,P18:R50,AB18:AC50,AN18:AQ50,AT18:AT50,D11")
            Case "Sheet19"
                Set rng = Union(.Range("C8:G8"), .Range("C9:G9"), .Range("C10:G10"), .Range("C11:G11"), .Range("C12:G12"), .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"))
            Case "Sheet21"
                Set rng = .Range("C31:N31,C40:N40")
            Case "Sheet24"
                Set rng = .Range("J3:U3,J7:U10,X11,X14,X16,J16:U16,J22,J23,J26,J29,J30,D42,F42")
            Case "Sheet25"
                Set rng = .Range("M29:M31")
            Case "Sheet28"
                Set rng = Union(.Range("C24:F24,I24,J24,M24,N24,P24"), .Range("n9:o9"), .Range("n10:o10"), .Range("p9:q9"), .Range("p10:q10"), .Range("D49,I5"))
            End Select
        End With
        If Not rng Is Nothing Then
            rng.ClearContents
        End If
    Next WS
End Sub
 
Upvote 0
Solution
rlv01 said:
If you do it another way, do you get the same kinds of error messages? For example
(not tested)
VBA Code: 
Sub ClearRanges1()
    Dim rng As Range
    Dim WS As Worksheet

    ' Loop through each relevant worksheet in the workbook and clear the ranges.
    For Each WS In ThisWorkbook.Worksheets
        Set rng = Nothing
        With WS
            Select Case .Name
            Case "Sheet2"
                Set rng = .Range("B18:K50,P18:R50,AB18:AC50,AN18:AQ50,AT18:AT50,D11")
            Case "Sheet4"
                Set rng = .Range("A2:AU2500")
            Case "Sheet8"
                Set rng = .Range("C10:H21")
            Case "Sheet11"
                Set rng = .Range("A2:BJ1379")
            Case "Sheet14"
                Set rng = .Range("F22,I24:I25")
            Case "Sheet15"
                Set rng = .Range("AG11:AI1368,AL11:AP1368")
            Case "Sheet16"
                Set rng = Union(.Range("E17:K1379,AY17:AY1379"), .Range("AY9:AY10"), .Range("AZ9:AZ10"), .Range("AZ3,c8,c9"))
            Case "Sheet17"
                Set rng = .Range("B18:K50,P18:R50,AB18:AC50,AN18:AQ50,AT18:AT50,D11")
            Case "Sheet19"
                Set rng = Union(.Range("C8:G8"), .Range("C9:G9"), .Range("C10:G10"), .Range("C11:G11"), .Range("C12:G12"), .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"))
            Case "Sheet21"
                Set rng = .Range("C31:N31,C40:N40")
            Case "Sheet24"
                Set rng = .Range("J3:U3,J7:U10,X11,X14,X16,J16:U16,J22,J23,J26,J29,J30,D42,F42")
            Case "Sheet25"
                Set rng = .Range("M29:M31")
            Case "Sheet28"
                Set rng = Union(.Range("C24:F24,I24,J24,M24,N24,P24"), .Range("n9:o9"), .Range("n10:o10"), .Range("p9:q9"), .Range("p10:q10"), .Range("D49,I5"))
            End Select
        End With
        If Not rng Is Nothing Then
            rng.ClearContents
        End If
    Next WS
End Sub
Click to expand...

Hi @rlv01 ,
This worked! 🙌👌👍🥳
Thank you so much. I can now move on with my life! 😂
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Simplify VBA code
Replies
2
Views
427
kevin9999
K
U
  • Question Question
Seeking Help with CELLS Property in VBA
2
Replies
13
Views
631
Alex Blakenburg
A
K
  • Question Question
Combine Multiple Excel Tables
Replies
4
Views
611
Kalim Shaikh
K
M
  • Question Question
VBA Sorting Column Issue
Replies
0
Views
468
mrstats_2288
M
V
  • Question Question
Creating a macro to rename worksheet tabs every month
Replies
6
Views
974
vickmoody
V

Forum statistics

Threads
1,223,912
Messages
6,175,341
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top