asked on

Clear Multiple Ranges in Multiple Worksheets

I wouls like to clear Multiple ranges value located in different Worksheets with a VBA script.

These are the Sheet anmes and the ranges.

Sub Clear_Multiple_Ranges()

Dim i As Integer
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim r4 As Range
Dim r5 As Range
Dim r6 As Range
Dim r7 As Range
Dim r8 As Range
Dim r9 As Range
Dim r10 As Range
Dim r11 As Range
Dim r12 As Range
Dim r13 As Range
Dim r14 As Range
Dim r15 As Range
Dim r16 As Range
Dim r17 As Range
Dim r18 As Range
Dim r19 As Range
Dim r20 As Range
Dim r21 As Range
Dim r22 As Range
Dim r23 As Range
Dim r24 As Range

Set r1 = Sheets("Report").Range("AIL")
Set r2 = Sheets("Report").Range("Execsum")
Set r3 = Sheets("Report").Range("Impeddisc")
Set r4 = Sheets("Report").Range("Collectexp")

Set r5 = Sheets("Inputs and Calcs").Range("Rptdate")
Set r6 = Sheets("Inputs and Calcs").Range("Recprof")
Set r7 = Sheets("Inputs and Calcs").Range("BSdrill1")
Set r8 = Sheets("Inputs and Calcs").Range("BSdrill2")
Set r9 = Sheets("Inputs and Calcs").Range("ABAL1")
Set r10 = Sheets("Inputs and Calcs").Range("ABAL2")
Set r11 = Sheets("Inputs and Calcs").Range("ABAL3")
Set r12 = Sheets("Inputs and Calcs").Range("Counts4C")
Set r13 = Sheets("Inputs and Calcs").Range("Recsum1")
Set r14 = Sheets("Inputs and Calcs").Range("Recsum2")
Set r15 = Sheets("Inputs and Calcs").Range("Recsum3")
Set r16 = Sheets("Inputs and Calcs").Range("Imped1")
Set r17 = Sheets("Inputs and Calcs").Range("Imped2")
Set r18 = Sheets("Inputs and Calcs").Range("Impedsum")
Set r19 = Sheets("Inputs and Calcs").Range("Lossshr1")
Set r20 = Sheets("Inputs and Calcs").Range("Lossshr2")
Set r21 = Sheets("Inputs and Calcs").Range("Lossshr3")
Set r22 = Sheets("Inputs and Calcs").Range("Prumoo")

Set r23 = Sheets("Raw Data BS").Range("NFEbs")

Set r24 = Sheets("Raw Data Trend").Range("NFEts")

End Sub

Cacique610

ASKER

Sorry it is an Excel 2003 vba

Rey Obrero (Capricorn1)

try this codes

Sub delRange()
Dim xlObj As Object, j As Integer
Set xlObj = CreateObject("excel.application")
xlObj.workbooks.Open "C:\myExcel.xls"
With xlObj
For j = .names.Count To 1 Step -1

.names(j).Delete
Next
End With
xlObj.Quit

End Sub

Glenn Ray

I'm not sure why you are assigning the ranges in a subroutine that is titled "Clear_Multiple_Ranges", but I suggest revising your code to assign the ranges to an array variable instead. You can then clear the ranges via a loop using your integer variable (i).

You would then probably have two separate routines as so:

Sub Assign_Multiple_Ranges()
    Dim r(24) As Range
    Set r(1) = Sheets("Report").Range("AIL")
    Set r(2) = Sheets("Report").Range("Execsum")
    Set r(3) = Sheets("Report").Range("Impeddisc")
    Set r(4) = Sheets("Report").Range("Collectexp")
    Set r(5) = Sheets("Inputs and Calcs").Range("Rptdate")
    Set r(6) = Sheets("Inputs and Calcs").Range("Recprof")
    Set r(7) = Sheets("Inputs and Calcs").Range("BSdrill1")
    Set r(8) = Sheets("Inputs and Calcs").Range("BSdrill2")
    Set r(9) = Sheets("Inputs and Calcs").Range("ABAL1")
    Set r(10) = Sheets("Inputs and Calcs").Range("ABAL2")
    Set r(11) = Sheets("Inputs and Calcs").Range("ABAL3")
    Set r(12) = Sheets("Inputs and Calcs").Range("Counts4C")
    Set r(13) = Sheets("Inputs and Calcs").Range("Recsum1")
    Set r(14) = Sheets("Inputs and Calcs").Range("Recsum2")
    Set r(15) = Sheets("Inputs and Calcs").Range("Recsum3")
    Set r(16) = Sheets("Inputs and Calcs").Range("Imped1")
    Set r(17) = Sheets("Inputs and Calcs").Range("Imped2")
    Set r(18) = Sheets("Inputs and Calcs").Range("Impedsum")
    Set r(19) = Sheets("Inputs and Calcs").Range("Lossshr1")
    Set r(20) = Sheets("Inputs and Calcs").Range("Lossshr2")
    Set r(21) = Sheets("Inputs and Calcs").Range("Lossshr3")
    Set r(22) = Sheets("Inputs and Calcs").Range("Prumoo")
    Set r(23) = Sheets("Raw Data BS").Range("NFEbs")
    Set r(24) = Sheets("Raw Data Trend").Range("NFEts")
End Sub

Sub Clear_Multiple_Ranges()
    Dim r(24) As Range
    Dim i As Integer
    For i = 1 To 24
        r(i).ClearContents
    Next i
End Sub

Open in new window

Rey Obrero (Capricorn1)

in excel use this

Sub delRange()

Dim j As Integer
With ActiveWorkbook
For j = .Names.Count To 1 Step -1
.Names(j).Delete
Next
End With
End Sub

Saqib Husain

I thought that the OP wanted to clear the values. If that is the case then he needs something like

r1.clearcontents
r2.clearcontents
r3.clearcontents
r4.clearcontents
.
.
.

Glenn Ray

Sorry, that's not correct.

The dimension statement for the range should be outside the subroutines on top and removed from the inside of each routine

Dim r(24) As Range
Sub Assign_Multiple_Ranges()
    Set r(1) = Sheets("Report").Range("AIL")
    Set r(2) = Sheets("Report").Range("Execsum")
    Set r(3) = Sheets("Report").Range("Impeddisc")
    Set r(4) = Sheets("Report").Range("Collectexp")
    Set r(5) = Sheets("Inputs and Calcs").Range("Rptdate")
    Set r(6) = Sheets("Inputs and Calcs").Range("Recprof")
    Set r(7) = Sheets("Inputs and Calcs").Range("BSdrill1")
    Set r(8) = Sheets("Inputs and Calcs").Range("BSdrill2")
    Set r(9) = Sheets("Inputs and Calcs").Range("ABAL1")
    Set r(10) = Sheets("Inputs and Calcs").Range("ABAL2")
    Set r(11) = Sheets("Inputs and Calcs").Range("ABAL3")
    Set r(12) = Sheets("Inputs and Calcs").Range("Counts4C")
    Set r(13) = Sheets("Inputs and Calcs").Range("Recsum1")
    Set r(14) = Sheets("Inputs and Calcs").Range("Recsum2")
    Set r(15) = Sheets("Inputs and Calcs").Range("Recsum3")
    Set r(16) = Sheets("Inputs and Calcs").Range("Imped1")
    Set r(17) = Sheets("Inputs and Calcs").Range("Imped2")
    Set r(18) = Sheets("Inputs and Calcs").Range("Impedsum")
    Set r(19) = Sheets("Inputs and Calcs").Range("Lossshr1")
    Set r(20) = Sheets("Inputs and Calcs").Range("Lossshr2")
    Set r(21) = Sheets("Inputs and Calcs").Range("Lossshr3")
    Set r(22) = Sheets("Inputs and Calcs").Range("Prumoo")
    Set r(23) = Sheets("Raw Data BS").Range("NFEbs")
    Set r(24) = Sheets("Raw Data Trend").Range("NFEts")
End Sub
Sub Clear_Multiple_Ranges()
    Dim i As Integer
    For i = 1 To 24
        r(i).ClearContents
    Next i
End Sub

Open in new window

Cacique610

ASKER

I am trying to clear the content

indeed
r#.ClearContent is needed.

Saqib Husain

Make sure it is

r#.clearcontents

and not

r#.clearcontent

Cacique610

ASKER

How do I run this?

Dim r(24) As Range
Sub Assign_Multiple_Ranges()
    Set r(1) = Sheets("Report").Range("AIL")
    Set r(2) = Sheets("Report").Range("Execsum")
    Set r(3) = Sheets("Report").Range("Impeddisc")
    Set r(4) = Sheets("Report").Range("Collectexp")
    Set r(5) = Sheets("Inputs and Calcs").Range("Rptdate")
    Set r(6) = Sheets("Inputs and Calcs").Range("Recprof")
    Set r(7) = Sheets("Inputs and Calcs").Range("BSdrill1")
    Set r(8) = Sheets("Inputs and Calcs").Range("BSdrill2")
    Set r(9) = Sheets("Inputs and Calcs").Range("ABAL1")
    Set r(10) = Sheets("Inputs and Calcs").Range("ABAL2")
    Set r(11) = Sheets("Inputs and Calcs").Range("ABAL3")
    Set r(12) = Sheets("Inputs and Calcs").Range("Counts4C")
    Set r(13) = Sheets("Inputs and Calcs").Range("Recsum1")
    Set r(14) = Sheets("Inputs and Calcs").Range("Recsum2")
    Set r(15) = Sheets("Inputs and Calcs").Range("Recsum3")
    Set r(16) = Sheets("Inputs and Calcs").Range("Imped1")
    Set r(17) = Sheets("Inputs and Calcs").Range("Imped2")
    Set r(18) = Sheets("Inputs and Calcs").Range("Impedsum")
    Set r(19) = Sheets("Inputs and Calcs").Range("Lossshr1")
    Set r(20) = Sheets("Inputs and Calcs").Range("Lossshr2")
    Set r(21) = Sheets("Inputs and Calcs").Range("Lossshr3")
    Set r(22) = Sheets("Inputs and Calcs").Range("Prumoo")
    Set r(23) = Sheets("Raw Data BS").Range("NFEbs")
    Set r(24) = Sheets("Raw Data Trend").Range("NFEts")
End Sub
Sub Clear_Multiple_Ranges()
    Dim i As Integer
    For i = 1 To 24
        r(i).ClearContents
    Next i
End Sub

Open in new window

Norie

Try this.

Dim rng As Range
Dim arrWSs
Dim arrRngs
Dim I As Long

arrWSs = Array("Report", "Report", "Report", "Report", _
                      "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", _
                      "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", _
                      "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", _
                      "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", _
                      "Inputs and Calcs", "Inputs and Calcs", "Raw Data BS", "Raw Data Trend")
arrRngs = Array("AIL", "Execsum", "Impeddisc", "Collectexp", "Rptdate", "Recprof", "BSdrill1", "BSdrill2", _
                      "ABAL1", "ABAL2", "ABAL3", "Counts4C", "Recsum1", "Recsum2", "Recsum3", "Imped1", _
                      "Imped2", "Impedsum", "Lossshr1", "Lossshr2", "Lossshr3", "Prumoo", "NFEbs", "NFEts")


For I = LBound(arrWSs) To UBound(arrWSs)

    Set rng = Worksheets(arrWSs(I)).Range(arrRngs(I))
   ' Debug.Print arrWSs(I) & "!" & arrRngs(I)
    rng.ClearContents
    
Next I

Open in new window

Cacique610

ASKER

I am getting error 1004

Dim rng As Range
Dim arrWSs
Dim arrRngs
Dim I As Long

arrWSs = Array("Report", "Report", "Report", "Report", _
                      "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", _
                      "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", _
                      "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", _
                      "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", _
                      "Inputs and Calcs", "Inputs and Calcs", "Raw Data BS", "Raw Data Trend")
arrRngs = Array("AIL", "Execsum", "Impeddisc", "Collectexp", "Rptdate", "Recprof", "BSdrill1", "BSdrill2", _
                      "ABAL1", "ABAL2", "ABAL3", "Counts4C", "Recsum1", "Recsum2", "Recsum3", "Imped1", _
                      "Imped2", "Impedsum", "Lossshr1", "Lossshr2", "Lossshr3", "Prumoo", "NFEbs", "NFEts")


For I = LBound(arrWSs) To UBound(arrWSs)

    Set rng = Worksheets(arrWSs(I)).Range(arrRngs(I))
   ' Debug.Print arrWSs(I) & "!" & arrRngs(I)
    rng.ClearContents
    
Next I

Open in new window

Norie

Does the original code you posted work?

That's where I took the worksheet and range names from.

Cacique610

ASKER

I was getting 1004 also

Saqib Husain

First farmer: What did you give to your cow when it was sick?
Second farmer: Turpentine oil.

A few days later

First farmer: I gave my cow turpentine oil and it died
Second farmer: So did mine.....

Norie

That could mean that some of the worksheet/range names are incorrect.

What's the actual error message?

A quick fix would be to add On Error Resume Next to the code but that might not be a good idea if you are automating Excel from Access.

If the code is in Excel this is how adding On Error would look.

For I = LBound(arrWSs) To UBound(arrWSs)
    On Error Resume Next
    Set rng = Worksheets(arrWSs(I)).Range(arrRngs(I))
   ' Debug.Print arrWSs(I) & "!" & arrRngs(I)
    rng.ClearContents
    
    On Error GoTo 0
    
Next I

Open in new window

Cacique610

ASKER

Run time error 1004
Application -Defined or object define error

then it points to this statement
Set rng = Worksheets(arrWSs(I)).Range(arrRngs(I))

Glenn Ray

imnorie is correct: one or more of your range names (and/or worksheet names) are either missing or not spelled exactly as you specified in the original post.

You need to review all the range names and ensure that they exist and then adjust the code appropriately.

Norie

The code I posted with the On Error added works fine for me in Excel VBA, and I have none of those worksheets or ranges.

Cacique610

ASKER

Wil try that really quick

Cacique610

ASKER

This is the code as it is in the Module. I found no errors on the range names and worksheets.

Sub Reset()


Dim rng As Range
Dim arrWSs
Dim arrRngs
Dim I As Long




arrWSs = Array("Report", "Report", "Report", "Report", _
                      "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", _
                      "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", _
                      "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", _
                      "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", "Inputs and Calcs", _
                      "Inputs and Calcs", "Inputs and Calcs", "Raw Data BS", "Raw Data Trend")
arrRngs = Array("AIL", "Execsum", "Impeddisc", "Collectexp", "Rptdate", "Recprof", "BSdrill1", "BSdrill2", _
                      "ABAL1", "ABAL2", "ABAL3", "Counts4C", "Recsum1", "Recsum2", "Recsum3", "Imped1", _
                      "Imped2", "Impedsum", "Lossshr1", "Lossshr2", "Lossshr3", "Prumoo", "NFEbs", "NFEts")


For I = LBound(arrWSs) To UBound(arrWSs)

    Set rng = Worksheets(arrWSs(I)).Range(arrRngs(I))
   ' Debug.Print arrWSs(I) & "!" & arrRngs(I)
    rng.ClearContents
    
Next I

End Sub

Open in new window

Cacique610

ASKER

Just in case but this is for Excel 2003.

Norie

The version shouldn't matter.

Is it working?

Glenn Ray

To see what range/sheet is causing the 1004 error, replace the following lines in imnorie's code (from lines 23-29 in your example above)

For I = LBound(arrWSs) To UBound(arrWSs)
    On Error Goto ErrOut
    Set rng = Worksheets(arrWSs(I)).Range(arrRngs(I))
    rng.ClearContents 
Next I
Exit Sub

ErrOut:
    Msgbox "Cannot find Range: " & arrRngs(I) & " on worksheet: " & arr(WSs(I)

Open in new window

Glenn Ray

oops...last line shouldn't have that extra opening paranthesis:

Msgbox "Cannot find Range: " & arrRngs(I) & " on worksheet: " & arrWSs(I)

Open in new window

Norie

Glenn

Won't that jump out of the loop when the first error occurs?

I can't make my mind up whether that's a good or bad thing.

If there's a few problems it might be OK but if there's a lot I'm not so sure.

I think what I would do is set it up to go through the whole loop and report the problem ranges at the end.

Glenn Ray

imnorie, yes it will.

IMO, if there are many errors, it calls into question the whole approach (i.e., not knowing the proper range names). I presumed that there would be just one or two possible errors (ex., spelling only).

And while I agree it would be more thorough to capture all possible errors and report afterward, solutions have been offered here on the assumption that the original data was accurate.

BTW, I like your array assignment best.

Norie

Glenn

I was kind of pinning my hopes on it being a misspelled sheet name rather than individual range names.

Or perhaps some sort of pattern, eg every range name has a trailing space.

If you had a log that might help.

Anyway, suppose we'll just need to wait and see.

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

rory

Not sure about that.

Rory Archibald

@imnorie,
You are correct - I miscounted the arrays.