Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
Sign in to follow this  
Kirabo

Code Tidy Up

Question

I have recorded a macro to do some operations I need to complete on imported data. It saves me some time and lets the VBA do the tedious bit of the task for me.

 

However, I know that recording macros isnt the most efficient task. Has anyone got any suggestions of how I can tidy this code up?

 

Code:

Sub Macro1()
'
' Macro1 Macro
'

'
    Rows("2:3").Select
    Selection.Delete Shift:=xlUp
    Range("A1:M2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A3:M4").Select
    Selection.Copy
    Range("A17").Select
    ActiveSheet.Paste
    Range("A22").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-19]C:R[-1]C)"
    Range("A22").Select
    Selection.AutoFill Destination:=Range("A22:M22"), Type:=xlFillDefault
    Range("A22:M22").Select
End Sub
 

 

Any help would be great

Kind Regards

Kirabo

Share this post


Link to post
Share on other sites

1 answer to this question

Recommended Posts

  • 0

Hello Kirabo, 

 

Thanks for the Query. I have consensed the code best I can, hopefully its clear to see where the inefficiences were eliminated:

Code:

Sub CodeTidy()
'Source: www.ExcelWTF.com

 

Rows("2:3").Delete Shift:=xlUp
With Range("A1:M2")
    .Interior.ThemeColor = xlThemeColorAccent1
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With

Range("A3:M4").Copy Range("A17")
Range("A22").FormulaR1C1 = "=SUM(R[-19]C:R[-1]C)"
Range("A22").AutoFill Destination:=Range("A22:M22"), Type:=xlFillDefault

End Sub

As you can see, removing all the SELECT statements saves a lot of line. You usually never would need to select a cell in Excel. Also when recording macros, Excel tends to verify related properties, and set thems to their default value. This is unnecessary as they are not changing and already at their default value (this can be see where you change the cell colour and alignment). 

 

Hope that helps

Caleeco

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×