Excel VBA ClearContents error

Question

Im trying to run macro which should clear contents from sheets AA, BB and CC and then move into sheet MENU but its highliting me an error on line 4. Please see the code below.

Sub clear_sheets()

Snames = Split(AA, BB, CC)

For Count = 0 To UBound(Snames)

    Sheets(Snames(Count)).Range("A3:C3").End(xlDown).ClearContents

Next
    Sheets("MENU").Select
Optimise (False)

End Sub

I got a little bit different approach posted on a different forum. Which would run more efficiently, I mean which one will will be putting less stress on a processing?

    Sub clear_sheets()

Optimise (True)

Snames = Split("AA, BB, CC", ", ")

For count = 0 To UBound(Snames)
MyRange = Range("A3:C3", Range("A3:C3").End(xlDown)).Address

    ThisWorkbook.Sheets(Snames(count)).Range(MyRange).ClearContents

Next
    Sheets("MENU").Select
Optimise (False)

End Sub

Update I understand this is not a code writing website but I would like to ask you if you could have a look at my code below.

Sub distribute_dsp_data_9()

Sheets("raw_data_1_9").Visible = True

Sheets("raw_data_1_9").Select
ActiveSheet.ListObjects("COMP_summ_9").Range.AutoFilter Field:=2, Criteria1 _
    :="DTTD"
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Copy
Sheets("DTTD").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("raw_data_1_9").Select
ActiveSheet.ListObjects("COMP_summ_9").Range.AutoFilter Field:=2, Criteria1 _
    :="FDTL"
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Copy
Sheets("FDTL").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("raw_data_1_9").Select
ActiveSheet.ListObjects("COMP_summ_9").Range.AutoFilter Field:=2, Criteria1 _
    :="FULL"
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Copy
Sheets("FUL ON").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("raw_data_1_9").Select
ActiveSheet.ListObjects("COMP_summ_9").Range.AutoFilter Field:=2

Sheets("raw_data_1_9").Visible = False

End Sub

What do you expect `AA`, `BB`, `CC`, to be? Sheet names? Those aren't declared so VBA is assuming they're some kind of variable I think... — BruceWayne, Dec 13 '16 at 22:47

CallumDA · Accepted Answer · 2016-12-13T23:03:24.743

3

Try this. I've declared your variables properly and also assigned values to your array using Array() rather than Split() both are fine, but Array() is more flexible

Sub clear_sheets()
    Dim sheetNames As Variant
    Dim count As Integer
    sheetNames = Array("AA", "BB", "CC")

    For Count = 0 To UBound(Snames)
        With Sheets(sheetNames(Count))
            Range(.Range("A3"), .Range("C3").End(xlDown)).ClearContents
        End With
    Next

    Sheets("MENU").Select
    Optimise (False)
End Sub

Also, it's better to use ThisWorkbook.Sheets()rather than just Sheets() if your code refers to the same workbook you are writing your VBA in. If you don't do this then VBA will assume you are referring to the sheets in whichever workbook is active when you run the code - that's not usually a good thing.

Update I've changed the code to delete what I think you might want deleted?

edited Dec 13 '16 at 23:03

answered Dec 13 '16 at 22:46

CallumDA

12,025
6
30
52

I'm afraid macro is deleting only a bottom left cell in every sheet rather then deleting the whole table [follow the link tot the image](https://s29.postimg.org/anfea8nmf/image.png) – Martin Dec 13 '16 at 22:58
1

@Martin it is not clear what exactly you want to delete: the last row? all the rows? the cells `A to C` from the last row? once you clarify it CallumDA can edit the answer accordingly. – A.S.H Dec 13 '16 at 23:02
Im sorry if it wasnt clear at the beginning. I want to delete all the contents by selecting A3:C3 and then pressing arrow down whilst holding Ctrl+Shift. I want to use this method because I will have different amount of rows containing data. One time it could be A3:C15 next time A3:C36 that i want to delete but the range will always start at A3. – Martin Dec 13 '16 at 23:30
Thanks for clarifying, I update the code accordingly – CallumDA Dec 13 '16 at 23:31
I have updated the original post, could you please review and tell me which one will be processed using less of computers resources. – Martin Dec 13 '16 at 23:52
I'm not an expert when it comes to these kind of questions but I'd say the solution I provided uses less resources because it doesn't create an extra variable for the address and also I think declaring variable types before you use the variables makes the code run quicker - `Variant` type would probably be an exception though – CallumDA Dec 13 '16 at 23:59
Thank you for your support, may I ask you to look at the code in my original post and tell me if I can squeeze it into the loop as you did with copy paste bit. Hope it's not too much what im asking from you. – Martin Dec 14 '16 at 16:56
Hi @Martin, this is not the right place for me to look at all of your code and improve it. You can post it on Code Review - but before you do so I'd recommend taking a look at this first for how to avoid using `Select` http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – CallumDA Dec 14 '16 at 22:54

score 0 · Answer 2 · answered Dec 13 '16 at 22:31

0

Use:

Snames = Split("AA", "BB", "CC")

answered Dec 13 '16 at 22:31

nbayly

2,167
2
14
23

I did it as you have suggested and now it highlights line 2. The line of your suggestion. – Martin Dec 13 '16 at 22:37

Excel VBA ClearContents error

2 Answers2