Clearing a Column range in Multiple Workbooks

  • Thread starter Thread starter Ruan
  • Start date Start date
R

Ruan

I have a command button on Sheet1 which I would like to use to clear
contents in a column range on Sheet1, Sheet2, and Sheet3. How do I do
this without Activating Sheet2 and Sheet3?


Code in the Sheet1 Module

Private Sub cmdClearCol5_Click()
ActiveSheet.Range("E11:E1000").ClearContents
[sheet2 code??]
[sheet3 code??]
End Sub

Thanks
Ruan
 
Ruan,

Try something like

Worksheets("Sheet1").Range("E11:E1000").ClearContents
Worksheets("Sheet2").Range("E11:E1000").ClearContents
Worksheets("Sheet3").Range("E11:E1000").ClearContents

If you want to clear the range on all worksheets, use a loop:

Dim WS As Worksheet
For Each WS In Worksheets
WS.Range("E11:E1000").ClearContents
Next WS


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Back
Top