Clearing in dividual cells across several worksheets using a button.

[Matt5353]

Hi Everyone.
Merry Xmas

I have a spreadsheet that has 5 sheets.
I wish to delete certain cells "only" not rows or columns.
1st row is E5:J5, 2nd G5:J5, 3rd-F5:J5, 4th-G5:J5, 5th-H5:J5, 6th-I5:J5, 7th-J5
I wish to have a button on a Master sheet that will delete all those cells at the same time.
Andrew

 

[Trebor76]

Hi Matt5353,

Try this:

Option Explicit
Sub Macro1()

    Dim varMySheet As Variant
    Dim varMyRange As Variant
    Dim lngArrayIndex As Long
    
    'Sheet names to have their cells cleared from.  Change to suit if necessary.
    varMySheet = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7")
    'Cell ranges to be cleared from the corresponding sheet i.e. Sheet1 will have cells E5:J5 cleared.
    varMyRange = Array("E5:J5", "G5:J5", "F5:J5", "G5:J5", "H5:J5", "I5:J5", "J5")
    
    Application.ScreenUpdating = False
    
    For lngArrayIndex = LBound(varMySheet) To UBound(varMyRange)
        Sheets(varMySheet(lngArrayIndex)).Range(varMyRange(lngArrayIndex)).ClearContents
    Next lngArrayIndex
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[Matt5353]

Hi Robert Thanks for that.
? I have tried it but nothing seemed to work.
My sheets have been named would I change the code to refllect that.
Or it would work anyway.
Andrew

 

[Trebor76]

Hi Andrew,

Not sure as it worked for me

You need to have the seven sheet names in the "varMySheet" array you want to clear the contents from via the corresponding range from the "varMyRange" array. So in my code cells G5:J5 would be cleared from worksheet "Sheet2".

If there's no data in the ranges it would like nothing has happened so make sure you have something in the ranges before you run the macro.

Robert