Clearing 2 sheets from A3 to last row with text

akg742

New Member
Joined
Mar 13, 2014
Messages
39
I have a spreadsheet with 2 tabs of data. When someone needs to run a new report, I have a macro that will clear out any numbers below the Headers in row 2 that were already there. This works great except when the user is starting with empty cells. Then the macro clears out the headers (something with the End(xlUp).Row, I assume). I tried checking for content in A3 of each tab but that will only work if both are empty or both are full and I can't guarantee this will be true.

VBA Code:
    Dim Lastrow1, Lastrow2 As Integer
    Lastrow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    Range("A3:V" & Lastrow1).Select
    Selection.Clear
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try
VBA Code:
Sub MM1()
    If ActiveSheet.UsedRange.Rows.Count = 2 Then Exit Sub
    Range("A3:V" & Cells(Rows.Count, 1).End(xlUp).Row).Clear
End Sub
 
Upvote 0
Try
VBA Code:
Sub MM1()
    If ActiveSheet.UsedRange.Rows.Count = 2 Then Exit Sub
    Range("A3:V" & Cells(Rows.Count, 1).End(xlUp).Row).Clear
End Sub
Thanks! I think this will only work if both tabs have content or the second one is already empty though. Otherwise, the macro will see the blank cells in the first tab and stop without checking the second. This is a template for co-workers to use and I have no way to know if they'll be consistent with clearing data.
 
Upvote 0
Well based on the code provided, it will work for the activesheet...how does you code progress to the next sheet in its current form ?
VBA Code:
Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.UsedRange.Rows.Count > 1 Then
     ws.Range("A3:V" & Cells(Rows.Count, 1).End(xlUp).Row).Clear
    End If
Next ws
End Sub
 
Upvote 0
Well based on the code provided, it will work for the activesheet...how does you code progress to the next sheet in its current form ?
VBA Code:
Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.UsedRange.Rows.Count > 1 Then
     ws.Range("A3:V" & Cells(Rows.Count, 1).End(xlUp).Row).Clear
    End If
Next ws
End Sub
I change sheets with Sheets("A").Select. I need to be able to clear A and B but not C, D, and E.
 
Upvote 0
This way then
VBA Code:
Sub MM1()
Dim ws As Worksheet
For Each ws In Sheets(Array("A", "B"))
    If ws.UsedRange.Rows.Count > 1 Then
     ws.Range("A3:V" & Cells(Rows.Count, 1).End(xlUp).Row).Clear
    End If
Next ws
End Sub
 
Upvote 0
I'm still having issues. The above code is clearing rows 1-3 in both worksheets for some reason.
 
Upvote 0
This way then
VBA Code:
Sub MM1()
Dim ws As Worksheet
For Each ws In Sheets(Array("A", "B"))
    If ws.UsedRange.Rows.Count > 1 Then
     ws.Range("A3:V" & Cells(Rows.Count, 1).End(xlUp).Row).Clear
    End If
Next ws
End Sub
I replied above but forgot to hit the reply button (I'm having a great morning).
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top