Macro to clear Data on Selected sheets

howard

Well-known Member
I have the following code to clear data on selected sheets. I would like to streamline the code to make it neater

It would be appreciated if someone could assist me

Code:
`````` Sub Clear_Data()

Sheets("Sheet1").select
Dim Lr1 As Long
Lr1 = Cells(Rows.Count, "D").Rows.End(xlUp).Row
With Range("D1:E" & Lr1)
.ClearContents
End With

Sheets("Extracted Data").Select
Dim Lr As Long
Lr = Cells(Rows.Count, "A").Rows.End(xlUp).Row
With Range("A1:B" & Lr)
.ClearContents

End With
Sheets("Output Accounts").Select

With Range("A1:B" & Lr)
.ClearContents

End With
Sheets("Input Accounts").Select
With Range("A1:B" & Lr)
.ClearContents
End With
Sheets("Consignment Vat").Select

With Range("A1:B" & Lr)
.ClearContents
End With

End Sub``````

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Untested.
Code:
`````` Sub Clear_Data()
Dim Lr As Long, sh As Worksheet
Dim Lr1 As Long
Sheets("Sheet1").Select
Lr1 = Cells(Rows.Count, "D").End(xlUp).Row
With Range("D1:E" & Lr1)
.ClearContents
End With
For Each sh In Worksheets(Array("Extracted Data", "Output Accounts", _
"Input Accounts", "Consignment Vat"))
Lr = sh.Cells(Rows.Count, "A").End(xlUp).Row
sh.Range("A1:B" & Lr).ClearContents
Next sh
End Sub``````

Thanks for the help, Joe. Code far more streamlined.

Also
Code:
``````Sub Clear_Data()
Sheets("Sheet1").Range("D1:E" & Sheets("Sheet1").Cells(Rows.Count, "D").Rows.End(xlUp).Row).ClearContents
Sheets("Extracted Data").Range("A1:B" & Sheets("Extracted Data").Cells(Rows.Count, "A").Rows.End(xlUp).Row).ClearContents
Sheets("Output Accounts").Range("A1:B" & Sheets("Output Accounts").Cells(Rows.Count, "A").Rows.End(xlUp).Row).ClearContents
Sheets("Input Accounts").Range("A1:B" & Sheets("Input Accounts").Cells(Rows.Count, "A").Rows.End(xlUp).Row).ClearContents
Sheets("Consignment Vat").Range("A1:B" & Sheets("Consignment Vat").Cells(Rows.Count, "A").Rows.End(xlUp).Row).ClearContents
End Sub``````

Thanks Michael, much appreciated

Replies
3
Views
107
Replies
7
Views
262
Replies
0
Views
252
Replies
4
Views
200
Replies
0
Views
424

1,211,679
Messages
6,103,244
Members
447,849
Latest member
4chase

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.

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

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