Forum Discussion

JoAvg's avatar
JoAvg
Brass Contributor
Jan 22, 2023
Solved

Macro to clear contents off of multiple sheets

I am in need of a macro that:

 

1. Clears contents from multiple sheets:

2. Hides shapes Tick_1,Tick_2,Tick_3,Tick_4,Tick_5,Tick_6

3. Brings up a msg box saying "DATA PURGED"

 

List of desired cleared cells follows:

LISTS!N2
LISTS!AJ2

FORMULAS!B2
FORMULAS!B3
FORMULAS!B4
FORMULAS!B5
FORMULAS!B7
FORMULAS!B8
FORMULAS!I1
FORMULAS!I5

TOMH!G2
TOMH!I2
TOMH!K2
TOMH!M2
TOMH!O2
TOMH!Q2
TOMH!S2
TOMH!U2
TOMH!W2
TOMH!Y2

TOMH!AW7
TOMH!AX7
TOMH!AY7
TOMH!AZ7
TOMH!BA7
TOMH!BB7
TOMH!BC7
TOMH!BD7
TOMH!BE7
TOMH!BF7

STASH!L2
STASH!B9:G48

 

Thank you!!

excel
Macros and VBA
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Jan 22, 2023

    JoAvg 

    Sub ClearStuff()
    Dim i As Long
    For i = 1 To 6
        Worksheets("As_Built").Shapes("Tick_" & i).Visible = False
    Next i
    Worksheets("FORMULAS").Range("B2:B5,B7:B8,I1,I5").ClearContents
    Worksheets("LISTS").Range("N2,AJ2").ClearContents
    Worksheets("STASH").Range("B9:G48,L2").ClearContents
    Worksheets("TOMH").Range("G2,I2,K2,M2,O2,Q2,S2,U2,W2,Y2,AW7:BF7").ClearContents
    MsgBox "DATA PURGED", vbInformation
End Sub
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jan 22, 2023

        JoAvg 

        Sub ClearStuff()
    Dim i As Long
    For i = 1 To 6
        Worksheets("As_Built").Shapes("Tick_" & i).Visible = False
    Next i
    Worksheets("FORMULAS").Range("B2:B5,B7:B8,I1,I5").ClearContents
    Worksheets("LISTS").Range("N2,AJ2").ClearContents
    Worksheets("STASH").Range("B9:G48,L2").ClearContents
    Worksheets("TOMH").Range("G2,I2,K2,M2,O2,Q2,S2,U2,W2,Y2,AW7:BF7").ClearContents
    MsgBox "DATA PURGED", vbInformation
End Sub

Resources