Clear entire rows below last row of data in col A except in 1st 3 sheets
 

I've got a file with 3 sheets placed leftmost (no action to be taken), then
in all the rest of the sheets to the right of the 3 leftmost sheets (a total
of 50+ sheets is to the right) I need to clear entire rows below the last
row of data in col A (within each sheet). Thanks.

Clear entire rows below last row of data in col A except in 1st 3 sheets
 

I'm thinking something like this should work...

Sub PartialClear()
Dim X As Long
Dim LastRow As Long
Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet
On Error Resume Next
Application.ScreenUpdating = False
For X = 4 To Worksheets.Count
With Worksheets(X)
.Activate
.Range(.Cells(Rows.Count, 1).End(xlUp), _
.Cells(Rows.Count, Columns.Count)).Clear
End With
Next
Application.ScreenUpdating = True
CurrentSheet.Activate
End Sub

Rick


"Max" wrote in message
...
I've got a file with 3 sheets placed leftmost (no action to be taken),
then in all the rest of the sheets to the right of the 3 leftmost sheets
(a total of 50+ sheets is to the right) I need to clear entire rows below
the last row of data in col A (within each sheet). Thanks.

Clear entire rows below last row of data in col A except in 1st 3sheets
 

Hi Max

Give this a try:

Sub Test()
Dim i As Long
On Error Resume Next
For i = 4 To Sheets.Count
With Sheets(i)
.Range(.Cells(Rows.Count, Columns.Count), .Cells(Rows.Count,
1).End(xlUp).Offset(1, 0)).Clear
End With
Next
End Sub

Richard




On 7 Jan, 07:42, "Max" wrote:
I've got a file with 3 sheets placed leftmost (no action to be taken), then
in all the rest of the sheets to the right of the 3 leftmost sheets (a total
of 50+ sheets is to the right) I need to clear entire rows below the last
row of data in col A (within each sheet). Thanks.

Clear entire rows below last row of data in col A except in 1st 3 sheets
 

Sub cleardown()
Dim xS As Integer
For xS = 4 To Worksheets.Count
With Worksheets(xS)
.Range(.Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1), _
.Cells(.Rows.Count, .Columns.Count)).ClearContents
End With
Next
End Sub

--

Regards,
Nigel




"Max" wrote in message
...
I've got a file with 3 sheets placed leftmost (no action to be taken),
then in all the rest of the sheets to the right of the 3 leftmost sheets
(a total of 50+ sheets is to the right) I need to clear entire rows below
the last row of data in col A (within each sheet). Thanks.

Clear entire rows below last row of data in col A except in 1st 3 sheets
 

Marvellous, Rick. Thanks. Runs great.

Clear entire rows below last row of data in col A except in 1st 3 sheets
 

Great, Richard. Thanks. Your sub runs well, too!

Clear entire rows below last row of data in col A except in 1st 3 sheets
 

Thanks, Nigel. Tried your sub. It runs well, too.