Code efficiency.. input needed

[buzz71023]

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.

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

 

[RickXL]

Hi,

The only thing that jumps out at me is:

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:

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

.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]

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.