Code cleanup assistance

Cossie

Active Member
Joined
May 6, 2002
Messages
323
I have written this for a combo box routine. Now the spreadsheet takes ages to open. I assume that this code is to much.
Can anyone suggest how i could trim it down or have other suggestions why my spreadsheet is so slow opening. here is the code i am using.

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Worksheets("Main").Range("f1").Value = ComboBox1.ListIndex + 1
Range("a16:d40").Select
Selection.Clear
Sheets("Staff").Select
Sheets("Staff").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
Worksheets("Staff").Range("a2:e5000").Copy
Worksheets("Main").Range("a16").PasteSpecial (xlPasteValues)
Selection.AutoFilter
Sheets("Main").Activate
Worksheets("Main").Range("a16").Activate
Range("a16").Select
Range("a16").Activate
'Range("a16:d40").Select
Selection.sort Key1:=Range("B16"), Order1:=xlAscending, Key2:=Range("C12" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Sheets("Main").Range("b3").Activate
Application.ScreenUpdating = True
End Sub


Tks for any assistance
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636
Avoid using "activate" and "select." It causes a lot of unneeded processor usage. You can reference cells without selecting them first. For example:

range("a1").select
selection.value = 1

could be:

range("a1").value = 1

Hope this helps.

Questions?

http://www.excelquestions.com
 

Cossie

Active Member
Joined
May 6, 2002
Messages
323
are you able to be a little more specific pls.

How would this code change?

Sheets("Staff").Select
Sheets("Staff").Activate
 

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636

ADVERTISEMENT

Set staff=sheets("Staff")


You can now reference that sheet by doing this:

staff.range("a1").value = "myvalue"

Questions?

http://www.excelquestions.com
 

Watch MrExcel Video

Forum statistics

Threads
1,122,388
Messages
5,595,889
Members
414,029
Latest member
mrwilker

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