Excel macro to delete data

[bthumble]

Hello Everyone,

Need help with an excel macro to clear data in specific columns on different sheets.

Sheet1 would start on A2 and clear columns A to AA, down to the last row of data.
Sheet2 would start on A2 and clear columns A to AG, down to the last row of data.
Sheet3 would start on K2 and clear columns K thru O, down to the last row of data.

Finally is there a way to only clear specific sheets using check boxes, and if no check boxes are selected then it would clear all three sheets?

Thanks for your help.

 

[Joe4]

For your rows with data, is there one particular column that will ALWAYS have data in all populated data rows?
If so, this makes it much easier to locate the last row with data.
Can you let us know which column that is?

 

[bthumble]

The first row in every sheet is column headers that will always stay.
Sheet 1 has data from A2-AA2 and formulas from AB2-AF2down to the last row of data. I want to keep the formulas in AB-AF, but clear data in A2-AA to the last row of data. The last row of data will always be on a different row.

Hope this helps.

 

[Joe4]

Try this:

Sub MyClearColumns()

    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim lr1 As Long, lr2 As Long, lr3 As Long
   
    Application.ScreenUpdating = False
   
'   Set three worksheet variables
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    Set ws3 = Sheets("Sheet3")
   
'   Find last rows of data on each sheet
    lr1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lr2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    lr3 = ws3.Cells(ws3.Rows.Count, "K").End(xlUp).Row
   
'   Clear contents of each sheet
    If lr1 > 1 Then ws1.Range("A2:AA" & lr1).ClearContents
    If lr2 > 1 Then ws2.Range("A2:AG" & lr2).ClearContents
    If lr3 > 1 Then ws3.Range("K2:O" & lr3).ClearContents
   
    Application.ScreenUpdating = True
   
End Sub

 

[bthumble]

That worked great. Many thanks.

 

[Joe4]

You are welcome.
Glad I was abnle to help!