Macro to clear Data on Selected sheets

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the help, Joe. Code far more streamlined.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,757
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows
Thanks Michael, much appreciated
 

Watch MrExcel Video

Forum statistics

Threads
1,122,820
Messages
5,598,297
Members
414,224
Latest member
Crazy_FC

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
Top