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]

This code shouldn't take long to run anyway. How long does it take, and what is your objective for performance?

 

[EssKayKay]

This is only a very small snipit of my entire project code. The entire coding involves over 1600 lines of code - of which I assume much of it is either redundant or sloppy. There are numerous calls throughout.

If you wish, I can post this. However, I suspect it may be a bit overwhelming as I noted, I am not a programmer so much of my code will simply amaze you, and not in a good way - but for the most party - it works.

Thank you both again for your time and consideration. It truly is appreciated.
Steve

 

[6StringJazzer]

Yeah, 1600 is a fair amount for a VBA project. I don't recommend posting it here. However, if you find that you still need help with it, can you share your file with a cloud service like Dropbox, OneDrive, or Google Docs?

 

[EssKayKay]

I'm going to work on this a bit more. If I think I need more help (which is most always the case) I'll be back with a dropbox upload. I'll keep you posted.

Once again Jeff - thank you,
Steve

 

[jolivanes]

What you can do is put a timer in the code, run it, notice the time, change all same type pieces of code run it again and see what the difference is.

 

[EssKayKay]

What you can do is put a timer in the code, run it, notice the time, change all same type pieces of code run it again and see what the difference is.

Good idea. I actually have a timer that can assist in that. I'll see what happens.
Thank you. . .

 

[jolivanes]

Make sure to use a copy of your full original workbook. If something goes wrong, it's gone forever. No "undo" button for a macro.

 

[EssKayKay]

Make sure to use a copy of your full original workbook. If something goes wrong, it's gone forever. No "undo" button for a macro.

Yup, I've learned that the hard way - more than once.

Take care. . .
SKK