asked on

Macro to clear particular rows and columns in multiple worksheets

I am having issues with a particular macro. It works correctly but it is also clearing the headers which I don't want it to do in the 'Current' tab as well as the 'Closed' tab. The button 'Restart Report' is where this macro is in place on the 'Table of Contents' tab. I want to keep the panes frozen the way they are as well. The macro should only clear all content from columns A through M (of the 'Current' and 'Closed' tabs) since there are formulas in columns N, O and P.
DSE-Carelog-Report.xlsm

Steve

your current code is:

Sub Cleardata()
    Dim sht As Worksheet

    For Each sht In ThisWorkbook.Worksheets
        Select Case sht.Name
        Case "Import", "Current", "Closed"
            With sht
                    .Range(.Cells(3, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 13).ClearContents
            End With
        End Select
    Next sht
    Sheets("Table of Contents").Range("b3").Value = "<Customer> Carelog"
End Sub

Open in new window

you need to check for data bafore clearing the sheet:

Sub Cleardata()
    Dim sht As Worksheet

    For Each sht In ThisWorkbook.Worksheets
        Select Case sht.Name
        Case "Current", "Closed" ' "Import" << removed
            With sht
                If .Cells(.Rows.Count, 1).End(xlUp) > 2 Then 'check for data on sheet
                    .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 13).ClearContents
                End If
            End With
        End Select
    Next sht
    Sheets("Table of Contents").Range("b3").Value = "<Customer> Carelog"
End Sub

Open in new window

This should fix the issues you have.

AckeemK

ASKER

I am testing it without any data on the sheet to see if it will clear the headers from A to M and it does sadly. I am trying to avoid that from occurring so the user does not have to replace it back. Is it due to the fact that there is no data in these tabs?

Rgonzo1971

HI,

pls try

Option Explicit
Sub Cleardata()
    Dim sht As Worksheet

    For Each sht In ThisWorkbook.Worksheets
        Select Case sht.Name
        Case "Import"
            With sht
                .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 13).ClearContents
            End With
        Case "Current", "Closed"
            With sht
                .Range(.Cells(2, 1), Cells(Application.Max(2, .Cells(.Rows.Count, 1).End(xlUp).Row), 1)).Resize(, 13).ClearContents
            End With
        End Select
    Next sht
    Sheets("Table of Contents").Range("b3").Value = "<Customer> Carelog"
End Sub

Open in new window

Regards

Steve

Why not keep it really really simple:

Option Explicit
Sub Cleardata()
    Dim SheetName
    
    For Each SheetName In Split("Import|Current|Closed", "|")
        Sheets(SheetName).Range("A2:M" & Sheets(SheetName).Rows.Count).ClearContents
    Next SheetName
    
    Sheets("Table of Contents").Range("B3").Value = "<Customer> Carelog"
    
End Sub

Open in new window

This will clear A to M from rows 2 to the end of each sheet in the string split by pipe.

AckeemK

ASKER

This macro above is not clearing the headers thankfully. However, it is not clearing the entire 'Import' tab which I need it do. When that sheet is not cleared and I import another file, it is shifted to the far right. If I can clear that whole sheet, that would work perfectly.

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial