Excel, VBA, .ClearContents with referenced range, error 1004

Question

I'm trying to use .clearcontents on range that is referenced by with some .offset, and I'm having trouble

I know that this works

Sub clear1_1()

Workbooks("xyz").Sheets("abc").range("A2:A3").ClearContents

End Sub

but if I try this it does not

Sub clear2()

Dim region As range
Set region = range("S509:AD618")
Workbooks("xyz").Worksheets("abc").range(region).ClearContents

end sub

I do understand from other postings, that it has something to do with object defyining, but I have no idea where I do mistake, what I need to write.

Final macro is run from one workbook, and is supposed to .clearcontents in other not activated workbook.

My code looks like this

sub Macro()

..... ton of code
Dim filename as string
dim sheetname as string
dim address3, address4 as string

filename = "xyz"
sheetname = "abc"  ' both variables that are loaded in other part

address3 and address4 loaded in other part


'here is where i get the error
sheets(sheetname).Range(Range(address3).Offset(0, 12).Address & ":" & Range(address4).Offset(-1, 23)).ClearContents


end sub

I can probably bypass it with .value="" But I'm looking to learn. Thank you for any response in advance.

EDIT 1 Hi Scott, doesn't make it. Posting bigger part of my code

If mapanchorsuccess = True And map1success = True And map2success = True Then
    If Workbooks(Filename).Sheets(startws).Range(address1).Offset(10, 13).HasFormula = True Then
                With Workbooks(wbm).Sheets("Report") 'report
                    .Range("A" & reportrow).Value = runnumber
                    .Range("B" & reportrow).Value = Filename
                    .Range("C" & reportrow).Value = Workbooks(Filename).Sheets(startws).Name
                    .Hyperlinks.Add anchor:=.Range("D" & reportrow), Address:=FilePath & Filename, SubAddress:=Workbooks(Filename).Sheets(startws).Name & "!A1"
                    .Range("E" & reportrow).Value = "Error"
                    .Range("F" & reportrow).Value = "rolling probably done already in this sheet"
                    reportrow = reportrow + 1
                End With

    Else
        With Workbooks(Filename).Sheets(startws)
            .Range(Range(address1).Offset(0, 12).Address & ":" & Range(address2).Offset(0, 14).Address).Copy _
            Range(Range(address1).Address & ":" & Range(address2).Offset(0, 2).Address)
                Application.CutCopyMode = False

            .Range(Range(address1).Offset(0, 16).Address & ":" & Range(address2).Offset(0, 16).Address).Copy _
            Range(Range(address1).Offset(0, 3).Address & ":" & Range(address2).Offset(0, 23).Address)
                Application.CutCopyMode = False

                    With Workbooks(wbm).Sheets("Report") 'report
                        .Range("A" & reportrow).Value = runnumber
                        .Range("B" & reportrow).Value = Filename
                        .Range("C" & reportrow).Value = Workbooks(Filename).Sheets(startws).Name
                        .Hyperlinks.Add anchor:=.Range("D" & reportrow), Address:=FilePath & Filename, SubAddress:=Workbooks(Filename).Sheets(startws).Name & "!A1"
                        .Range("E" & reportrow).Value = "Completed"
                        .Range("F" & reportrow).Value = "region1 rolled forward"
                        reportrow = reportrow + 1
                    End With


            .Range(Range(address3).Offset(0, 12).Address & ":" & Range(address4).Offset(-1, 23).Address).Copy _
            Range(Range(address3).Address & ":" & Range(address4).Offset(-1, 11).Address)

            '///// here the error 1004 occurs
            .Range(Range(address3).Offset(0, 12).Address & ":" & Range(address4).Offset(-1, 23).address).clearcontent
        End With
    End If
End If

https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 — Tim Williams, Jul 28 '16 at 16:14
It would have worked if you followed what I put. You need to put the `.` in front of each Range inside the `()` Use this which is exactly like the one I provided below `.Range(.Range(address3).Offset(0, 12), .Range(address4).Offset(-1, 23)).clearcontent` — Scott Craner, Jul 28 '16 at 16:26
It was missing an `s` on the end `.Range(.Range(address3).Offset(0, 12), .Range(address4).Offset(-1, 23)).clearcontents` — Scott Craner, Jul 28 '16 at 16:36
Getting error 438 for a change. `'///// here the error 1004 occurs .Range(.Range(address3).Offset(0, 12).Address & ":" & .Range(address4).Offset(-1, 23).Address).clearcontent End With` — Matus, Jul 28 '16 at 16:39
It seems it works correctly now. Thousend times thank you. The way I understand it , is that each .Range(address.... when compounding the range, says to excel specifically which workbook.sheet.cell use in compounding. Correct? Again thank you for your time and patience — Matus, Jul 28 '16 at 16:52

score 0 · Accepted Answer · answered Jul 28 '16 at 15:56

The workbook and sheet need to be set with the variable.

Then when using it since it is a range itself just refer to it.

Sub clear2()

Dim region As range
Set region = Workbooks("xyz").Worksheets("abc")range("S509:AD618")
region.ClearContents

end sub

As to your next code; that is a different problem. The ranges inside the () need to allocated to the correct sheet parentage or it will use the active sheet.

The easiest is with a With block:

With sheets(sheetname)
    .Range(.Range(address3).Offset(0, 12), .Range(address4).Offset(-1, 23)).ClearContents
End With

score 0 · Answer 2 · edited Oct 06 '21 at 14:46

I had this same issue, but it turned out to be very simple. I had a row of cells merged together between columns E and F, so when I used this command I had to set the ClearContents from the top corner of my E column to the bottom row of my F column.

What did not work:

Range("E1:E10").Clear Contents

What did work:

Range("E1:F10").ClearContents

I can't believe such a simple thing left me so thwarted.

Excel, VBA, .ClearContents with referenced range, error 1004

2 Answers2