VBA error 'Cannot change part of a merged cell'

[arooney88]

Having a problem with the VBA error "Cannot change part of a merged cell"

Here is my code. It clears the range on the first sheet, then hides the Raw Samples sheet. The loop then clears the ranges on the unhidden sheets in the workbook because they are formatted the exact same. This code only works if I go into all the sheets and clear any formatting for merged cells. I need this code to run on a number of workbooks so having to do this would be very time consuming or I'd have to add code to clear users formats before clearing ranges.

My questions are 1: Does a for next loop still check for formatting on a hidden sheet? 2: Is there a way to get around having to unformat a sheet but still clear the specified ranges?

sheets("Raw Samples").select
    Range("A9:AB3000").select
    selection.clearcontents
    activewindow.selectedsheets.visible = False
    
dim ws as worksheet
    for each ws in activeworkbook.worksheets
    ws.range("B3:G342").clearContents
    next ws
    
activeworkbook.sheets("Raw Samples").visible = true

 

[arooney88]

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim boolHidden As Boolean

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Raw Samples" Then
            Set rng = ws.Range("B3:G342")

            If ws.Visible = False Then
                boolHidden = True
                ws.Visible = True
            End If

            Application.Goto Reference:=rng
            Selection.ClearContents

            If boolHidden = True Then
                ws.Visible = False
                boolHidden = False
            End If
        End If
    Next ws
End Sub

This is the answer that got worked out. It will clear the range you determine without clearing the sheet you specify. This won't care about formatting.