CODEWITHSUNDEEP
vbaexcel
simpatico
simpatico

Reputation: 11

clear contents of array of sheets from specific row

I have three sheets i want to clear the contents of them from a specific row in one step .

So i tried this code..but then i have to repeat it for every sheet 

Worksheets("Sheet1").Rows(5 & ":" & Worksheets("Sheet1").Rows.Count).ClearContents

so what i want is treating sheets as an array & clearing the contents of them from specific row in one step

Like the following code .. but this code clear them all & i want to keep some rows 

 Sheets(Array("Sheet1", "Sheet2", "Sheet3").Select
 Sheets("Sunday").Activate
 Cells.Select
 Selection.ClearContents

Upvotes: 0

Views: 1054

Answers (1)

Smith
Smith

Reputation: 98

jsotola is correct that there is no way to do this in one step. However, there is a way to do this in one code block, so you don't have to repeat yourself.

If these are the only sheets in the workbook, you can loop through the Workbook.Worksheets collection and clear contents for each one.

Dim ws as Worksheet
For Each ws in ThisWorkbook.Worksheets
    With ws
       .Rows("5:" & .Rows.Count).ClearContents
    End With
Next ws

If there are other sheets in the workbook that you don't want to touch, you can adjust your loop accordingly with If statements, example below.

If ws.Name = "Sheet1" or ws.Name = "Sheet2" or ws.Name = "Sheet 3" Then

Upvotes: 2

Related Questions