How to clear range in selected multiple worksheet

[czarpogi]

Hi,

I need some help in making this code short and simple. Is there a way to do that? Thanks in advance.

Here's my code:

Sub ClearData()
With Sheets("NPS")
    .Range("A2", .Cells(.Rows.Count, .Columns.Count)).ClearContents
End With
With Sheets("QFY")
    .Range("A2", .Cells(.Rows.Count, .Columns.Count)).ClearContents
End With
With Sheets("MFY")
    .Range("A2", .Cells(.Rows.Count, .Columns.Count)).ClearContents
End With
With Sheets("W1")
    .Range("A2", .Cells(.Rows.Count, .Columns.Count)).ClearContents
End With
With Sheets("W2")
    .Range("A2", .Cells(.Rows.Count, .Columns.Count)).ClearContents
End With
With Sheets("W3")
    .Range("A2", .Cells(.Rows.Count, .Columns.Count)).ClearContents
End With
With Sheets("W4")
    .Range("A2", .Cells(.Rows.Count, .Columns.Count)).ClearContents
End With
With Sheets("W5")
    .Range("A2", .Cells(.Rows.Count, .Columns.Count)).ClearContents
End With
End Sub
[code/]

 

[My Aswer Is This]

Your wanting to clear contents all cells in Column "A" starting in row (2) of each of these sheets

Is this all the sheets in your workbook?

 

[czarpogi]

I have other sheets as well. But I only want to clear the content of the selected worksheet.

 

[My Aswer Is This]

You want to clear the entire contents of these sheets. Your script would not clear row(1)

I have other sheets as well. But I only want to clear the content of the selected worksheet.

 

[czarpogi]

Not the entire content. I want to keep row(1) and just clear the date starting from row(2) or from the range("A2"). Thanks.

 

[Michael M]

If you want to keep the header row, as your code suggests, use

Sub MM1()
Dim sh As Variant
For Each sh In Array("NPS", "QFY", "MFY", "W1", "W2", "W3", "W4", "W5")
Range("A2", Cells(Rows.Count, Columns.Count)).ClearContents
Next sh
End Sub

 

[My Aswer Is This]

I guess Michael has the answer now. So I will move on.

 

[Michael M]

No MAIT....cant test....don't have excel....so may not work !!

AAHHH....just realised....range needs to be cells

Sub MM1()
Dim sh As Variant
For Each sh In Array("NPS", "QFY", "MFY", "W1", "W2", "W3", "W4", "W5")
Sheets(sh).Activate
Range(Cells(2, 1), Cells(Rows.Count, Columns.Count)).ClearContents
Next sh
End Sub

 

[czarpogi]

Thanks Michael. I just tried the code, however it only clears the first sheet. I don't know if I'm missing something, but I just copied the code you've suggested

If you want to keep the header row, as your code suggests, use

Sub MM1()
Dim sh As Variant
For Each sh In Array("NPS", "QFY", "MFY", "W1", "W2", "W3", "W4", "W5")
Range("A2", Cells(Rows.Count, Columns.Count)).ClearContents
Next sh
End Sub

 

[czarpogi]

Ok i'll try this one.

No MAIT....cant test....don't have excel....so may not work !!

AAHHH....just realised....range needs to be cells

Sub MM1()
Dim sh As Variant
For Each sh In Array("NPS", "QFY", "MFY", "W1", "W2", "W3", "W4", "W5")
Sheets(sh).Activate
Range(Cells(2, 1), Cells(Rows.Count, Columns.Count)).ClearContents
Next sh
End Sub