Clear data from sheet 11

[howard]

I have tried to write code to clear data from sheet 11 to the last sheet from row A25 to the last row in Col H

The Formula clears data above this as well

I need the formula amended so that it only clears data from A25 to the last row containing data and also not to clear formulas

It would be appreciated if someone could assist me

 Sub Clear_Branch_Data_Sheets()
 Dim Sh As Worksheet
    Dim LR As Long
    For I = 11 To Worksheets.Count
    
    
    With Worksheets(I)

    For Each Sh In ActiveWorkbook.Worksheets
        With Sh
               LR = Cells(Rows.Count, "A").End(xlUp).Row
    
    End With
Range("a25:H" & LR).ClearContents
    
Next Sh
   End With
    Next I
End Sub

 

[dmt32]

Hi,
not tested but try:

Sub Clear_Branch_Data_Sheets()
    Dim Sh As Integer
    Dim LR As Long
    
    For Sh = 11 To Worksheets.Count
        With Worksheets(Sh)
            LR = .Cells(.Rows.Count, "A").End(xlUp).Row
            If LR < 25 Then LR = 25
            .Range("A25:H" & LR).SpecialCells(xlCellTypeConstants).ClearContents
        End With
    Next Sh
   
End Sub

Code assumes that there are no chart sheets between sheet 11 to last sheet.

Dave

 

[howard]

Thanks for the help, Dave

When running the macro, it comes up with a message, "no cells found" and the following code is highlighted

  .Range("A25:H" & LR).SpecialCells(xlCellTypeConstants).ClearContents

I also need one small change. Any colored items using the fill color not to be cleared

It would be appreciated if you could kindly amend the code

 

[dmt32]

you get that error if the cells are empty - need to add some error handling in the code something like:

             On Error Resume Next
            .Range("A25:H" & LR).SpecialCells(xlCellTypeConstants).ClearContents
            On Error GoTo 0

Dave

 

[howard]

Thanks Dave