alternative to Select/activate - to delete all data in a worksheet

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
400
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hello - i am trying to improve my VBA and speed it up by removing all instances of select and activate. one issue i am having is converting the below:

Workbooks("t.xlsx").Activate
sheets("main").Activate
Range("A2:X2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete
ActiveWorkbook.Close SaveChanges:=True

i thought this would work but it doesn't seem to be:

Workbooks("t.xlsx").sheets("main").Range("A2:X2").End(xlDown)).Delete
ActiveWorkbook.Close SaveChanges:=True
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Ok, use
VBA Code:
With Workbooks("t.xlsx").Sheets("main")
   .Range("A2", .Range("X" & Rows.count).End(xlUp).Row).Delete
End With
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
400
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Ok, use
VBA Code:
With Workbooks("t.xlsx").Sheets("main")
   .Range("A2", .Range("X" & Rows.count).End(xlUp).Row).Delete
End With
ok thanks x1down worked too. not sure if better to stay with x1up or not? your thoughts?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
I always use xlUp, If you have blank cells in the data xlDown will stop at the blank & not cover all the data.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
400
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

M
I always use xlUp, If you have blank cells in the data xlDown will stop at the blank & not cover all the data.
my data file could be 100,000 lines I guess would speed play a role in either? is one faster over the other :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
No difference.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

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