Code efficiency.. input needed

buzz71023

Active Member
Joined
May 29, 2011
Messages
281
I more or less need some input on if this code could be more efficient. If it can be faster please let me know how. All input is much appreciated, even if it is as simply as it's good as is.

Code:
Dim MyTest As Range, AshF As Range, AshT As Range
Dim AshH As Range, AshL As Range, myRng As Range
Dim TestSpecs As Range

Set MyTest = Sheets("Test Sheet").Columns("D:D")
Set AshF = Sheets("Test Sheet").Range("Ash3Freq")
Set AshT = Sheets("Test Sheet").Range("Ash3Trgt")
Set AshH = Sheets("Test Sheet").Range("Ash3Hi")
Set AshL = Sheets("Test Sheet").Range("Ash3Low")
Set myRng = Sheets("Data Sheet").Range("TestColumn")
Set TestSpecs = Sheets("Test Sheet").Range("D9:D12")

AshF = ComboBox1.Value
AshT = TextBox1.Value
AshH = TextBox2.Value
AshL = TextBox3.Value
  
  MyTest.Copy
  myRng.Insert shift:=xlRight
  TestSpecs.ClearContents
      
Unload Me
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
Hi,

The only thing that jumps out at me is:
Code:
Set MyTest = Sheets("Test Sheet").Columns("D:D")
Is it really necessary to copy the whole 1048576 rows?
The only other thing that might be a problem is the "TestColumn" range. Is that an entire column as well?

If using only part of a column works for you then you could try something like:
Code:
Sub Test()
    Dim MyTest As Range
    With Sheets("Test Sheet")
        Set MyTest = .Range("D1:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
    End With
End Sub
Code:
.Cells(.Rows.Count, "D").End(xlUp).Row
starts from the last row (.Rows.Count) in column D and finds the first occupied cell going upwards.
 

buzz71023

Active Member
Joined
May 29, 2011
Messages
281
Thanks, I didn't really notice a difference. But to answer your questions, I am actually only using D1-D41 but from D15 down to D41 there are no text, only formatting. TestColumn is just a cell.

I was hoping to make it a little faster because it was built on Excel 2010 and ran perfect on 2010. But my work updated to 2013 late last year and I have a serious lag. I've "Disabled hardware graphics acceleration" and charged my theme to gray with no background in excel. In Windows 7 performance options I unchecked "Animate controls and elements inside windows" and "Animate windows when minimizing and maximizing". My best guess would be the Virtual Memory or Total paging file for all drives, it's currently 4004 MB but I don't have administrative privileges to change this.

I know I probably need a new thread about this but there are so many out there already I figured I would at least bring it up.

Thanks again for the assistance.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,268
Messages
5,635,184
Members
416,846
Latest member
ImGoing2needaFormula

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