Cleaning up some "Select" code

[EssKayKay]

Hello,
I’m asking for some suggestions here if you please.

I have been informed eliminating some "Select" commands may speed up VBA routines. I have numerous situations where I believe I could streamline my coding. Here are a few examples. If I can clean these up, I will work on the others.

'#1 –
Range("K8,K9,K11:K13").Select
Selection.Locked = True
Selection.FormulaHidden = False
   With Selection.Interior
      .Color = 15984868
      .TintAndShade = 0
   End With

'#2 –
If Range("K8") > 0 And Range("K9") <= 0 Then
Range("K9").Select
Selection.Locked = False
Selection.FormulaHidden = False
   With Selection.Interior
      .Pattern = xlNone
      .TintAndShade = 0
   End With
End If

'#3 –
   Range("I33").Select
   Selection.Copy
   Range(Selection, Selection.End(xlDown)).Select
   ActiveSheet.Paste
   Application.CutCopyMode = False
   Range("M33:M" & Range("LastDataRow").Value).ClearContents
   Range("F6:F8,F10,K6:K13,F15:F17,F22").ClearContents

'#4 –
   Range("R34").Select
   Selection.Copy
   Range("R34:R133").Select
   ActiveSheet.Paste
   Application.CutCopyMode = False

'#5 –
   Range("P33:P" & Range("LastDataRow").Value).Select
   Selection.ClearContents
   Range("LookupFormula").Select
   Selection.Copy
   Range("P33").Select

Thanks for viewing,
Steve K.

 

[6StringJazzer]

If you rewrite the first one as follows, I think you will see the principles involved in cleaning up the others. The idea is that instead of Selecting something and working on Selection, refer directly to the thing you want to work on.

With Range("K8,K9,K11:K13")
    .Locked = True
    .FormulaHidden = False
    With .Interior
       .Color = 15984868
       .TintAndShade = 0
    End With
End With

It will speed it up slightly but what will speed it up even more is to use

Application.ScreenUpdating = False

as the first line of code, then repeat with True at the end.

 

[jolivanes]

You have some already where you use "With.....End With"
Do the same with the selects. Delete "Select" and in front put the "With"
Use "End With" at the end for each "With" you use
For #1 it would be

Deleted. Jeff was a couple minutes faster again

Do the same for all the others.
Remember to use the full stop (.) where applicable.

 

[EssKayKay]

Thank you both for your suggestions. Without being too demanding here (which I surely do not mean to be), could you give me a few more examples to my five samples above? There are dozens of these conditions throughout my program. I will then try to incorporate all your solutions accordingly.

Again, I don't mean to be burdensome,
Steve K.

 

[6StringJazzer]

Here is the principle. Instead of calling Select then operating on Selection (simple example):

Range("A1").Select
Select.Copy

operate directly on the object

Range("A1").Copy

If you have multiple operations for that object, speed things up even more by using With:

With Range("A1")
   .Interior.Color = RGB(255,0,0)
   .Copy
End With

Here is #3 done for you. This is a little different. Since the reference to Selection in Range is not using properties or methods, just by itself, it can't be subject to a With statement so I created a Range variable instead. Also, the copy and paste can be done using the Copy method in one line, if you want to emulate the worksheet copy/paste. If you want to just paste values, formats, etc., then you need two steps with Copy and Paste Special.

It looks like the copy from/paste to are on the same worksheet, which is also the active sheet. If that's not correct let me know.

'#3 –
   Dim R As Range
   Set R = Range("I33")
   R.Copy Range(R, R.End(xlDown))
   Range("M33:M" & Range("LastDataRow").Value).ClearContents
   Range("F6:F8,F10,K6:K13,F15:F17,F22").ClearContents

 

[EssKayKay]

Thank you Jeff. This will be helpful. This scenario happens numerous times. I'll see what happens.

 

[EssKayKay]

One more if you please
Is this correct?

BEFORE -

    Range("C33:C2032").Select
    Selection.Font.Bold = False
    With Selection.Font
        .ColorIndex = xlAutomatic
    End With

AFTER -

    With Range("C33:C2032").Font
        .Bold = False
        .ColorIndex = xlAutomatic
    End With

 

[jolivanes]

Re: "Is this correct?"

What is the result when youtried it?

 

[EssKayKay]

So far it appears to be working. However, I'm not really seeing any performance gain. Testing will take some time as variations of this code appear numerous times. From your view, does it look to be OK?

 

[MARK858]

I'm not really seeing any performance gain.

You see won't much of a noticeable gain with the original code you posted as it is the number of times you interact with the spreadsheet that causes the delay and you are only doing it 8 times in total, the last code is fine.