Which macro shortcut keys do you use?

A quick poll: What shortcut keys do you use for your macros, and what macros are they assigned to? Here are mine.

They're not written as well as they could be, but I don't care. They work and that's good enough for me (until I get really bored; then I'll fix them).

Shortcut key: Ctrl+Shift+P
Macro name: CopyPasteValues
Description: Converts formulas in selected range to their values
Macro:

Sub CopyPasteValues()

Dim rng As Excel.Range

  If TypeName(Selection) <> "Range" Then
    Exit Sub
  End If

  Set rng = Selection

  rng.Copy

  rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                          :=False, Transpose:=False
  Application.CutCopyMode = False

End Sub

A quick way to remove formulas and paste in the underlying data.

Shortcut key: Ctrl+Shift+F
Macro name: FilterToggle
Description: Adds autofilter arrows to selected range (usually row 1)
Macro:

Sub FilterToggle()
' add data filter arrows
' if error occurs, just skip
  On Error Resume Next
  Selection.AutoFilter
End Sub

Apply autofilter arrows to any worksheet. Just select the appropriate row first.

Shortcut key: Ctrl+Shift+H
Macro name: FormatHeaderRow
Description: Applies autofilter arrows, bold and color formatting to row 1, centers row 1 text and autofits all columns
Macro:

Sub FormatHeaderRow()

  Application.ScreenUpdating = False

  If ActiveSheet.AutoFilterMode = False Then
    With Range(Range("A1"), Range("IV1").End(xlToLeft))
      .AutoFilter
      .Font.ColorIndex = 2
      .Font.Bold = True

      With .Interior
        .ColorIndex = 43
        .Pattern = xlSolid
      End With

      .HorizontalAlignment = xlCenter
      .WrapText = False
      .Columns.AutoFit
    End With

    '    Range("A2").Select
    '    ActiveWindow.FreezePanes = True
  Else
    MsgBox _
    "Cannot autofilter the header row, there is already an autofilter on this sheet", vbCritical
  End If

  Application.ScreenUpdating = True
End Sub

This macro makes the header row (usually row 1) look exactly how I like it. The autofilter arrows are applied. The cells' text is centered. The font is bold, white with green background (I change this depending on my mood). Finally, the columns are autofit so I can see the entire header and all the data.

Optionally, I can freeze the window pane so that the header stays on the screen when scrolling.

Shortcut key: Ctrl+Shift+S
Macro name: SetNormal
Description: Removes formatting from worksheet and makes it look "normal" (according to me)
Macro:

Sub SetNormal()
' set worksheet to normal
  Application.ScreenUpdating = False

  If TypeName(Selection) <> "Range" Then Exit Sub

  Dim rng As Excel.Range
  Set rng = Selection

  If rng.cells.count = 1 Then
    Set rng = ActiveSheet.UsedRange
    ActiveWindow.Zoom = 80
  End If

  With rng
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone

    With .Font
      .Bold = False
      .Name = "Tahoma"
      .ColorIndex = 0
      .Size = 10
    End With

    .Interior.ColorIndex = xlNone
    .HorizontalAlignment = xlGeneral
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    .WrapText = False
    .Rows.AutoFit
    .Columns.AutoFit
  End With

  Application.ScreenUpdating = True
End Sub

My "normal" is different from yours. I hate getting spreadsheets with different fonts, font sizes and colors. When I just want to work with the data and not be distracted by the layout, I run this macro. It sets the window zoom to 80%, with a Tahoma font (size 10). It removes any border lines and cell colors. It re-orients any cell to General alignment and unwraps any wrapped cells. Finally, it autofits all rows and columns so all data is visible.

Shortcut key: Ctrl+Shift+U
Macro name: TogglePersonalXls
Description: Hides and unhides personal.xls workbook
Macro:

Sub TogglePersonalXls()
' toggle personal.xls workbook hidden status
Dim window As Boolean
  window = Windows("PERSONAL.XLS").Visible
  Windows("PERSONAL.XLS").Visible = Not window
End Sub

I don't use this one much anymore, but the shortcut key is still there in case I need it.

So what shortcut keys do you use, and what macros are assigned to them?

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 5 Comment(s) on Which macro shortcut keys do you use?:

  1. Hi,

    A. Numberformat that i've linked to a button.

    Sub TomsNumberFormat()
      Selection.NumberFormat = "#,##0"
    End Sub
    

    .

    B. Change comments in something looking more attractive => Also with a personal button

    Sub Comments_Tom()
    Dim MyComments As Comment
    Dim LArea As Long
    For Each MyComments In ActiveSheet.Comments
    With MyComments
    .Shape.AutoShapeType = msoShapeRoundedRectangle
    .Shape.TextFrame.Characters.Font.Name = "Tahoma"
    .Shape.TextFrame.Characters.Font.Size = 8
    .Shape.TextFrame.Characters.Font.ColorIndex = 2
    .Shape.Line.ForeColor.RGB = RGB(0, 0, 0)
    .Shape.Line.BackColor.RGB = RGB(255, 255, 255)
    .Shape.Fill.Visible = msoTrue
    .Shape.Fill.ForeColor.RGB = RGB(58, 82, 184)
    .Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
    End With
    Next 'comment
    End Sub
    

    .

    Looking forward form more timesavers…. ;)

  2. Danièle writes:

    Thanks for sharing those "quick fixes" macros that actually save so much time.
    The set normal is for me the best, but then all of them are great so that I know for sure that I'll be using them all pretty soon. The main issue now is to ensure keyboard short cuts that do not clash with other macros!
    Thanks!

  3. Yuanmin writes:

    Hi,

    Thanks for sharing, excellent job. I've done the similar and thought that it might also be helpful to add the following, please correct me if I'm made any mistake or overlooked something else

    [Comment Switching]:
    Switch between cell content & comment. Would save a lot of time of ”right-click" ->"Add-comment". Also sometimes useful when debugging the formulas.

    [Select all Shapes / Charts on the active sheet]:
    In some cases when the worksheet is crowded with dozens of charts/shapes, deleting them would be a pain in the arse, it would be very annoying trying to find them one by one in the first place.

    [Number Format Quick Switch]
    Quickly switching between some of the most common number format:
    e.g. "#,##0“ > ”0.0%" > "General"

    [Increase / decrease decimal points]:
    Instead of hitting the "increase/decrease decimal points" button every time when you want to unify a section of your data, just press the hot-key, saves tons of time.

    [Switch Status-bar Auto Calculation Mode]:
    The quick summary feature comes in handy when you're very quickly browsing through the data-set, but switching between "Count“ and "Sum" would be a very time-consuming process especially when you want to look at the data from different perspective back and forth. Just hit the macro.

    [Insert rows / columns]:
    I'm not sure if Excel has a hot-key already built in without popping up another dialog asking too much obvious questions. But i found it comes in handy from time to time.

    I've implementation of my own but I'm not sure if this is the good place to post them up. But I think we share the common idea /goal which is to fully utilize the amount of time we spent on ACTUAL operations.

    That's my two cents.

    Regards,
    YM

  4. Rick Rothstein (MVP - Excel) writes:

    I think this shorter macro does the same thing as your posted CopyPasteValues macro…

    Sub CopyPasteValues()
      If TypeOf Selection Is Range Then Selection.Value = Selection.Value
    End Sub
    

Mentions:

  1. [...] all that code into add-ins. Well, I finally did it. Last week, Code for Excel and Outlook posted Which Macro Shortcut Keys Do You Use. That seems like a good opportunity to layout what I [...]

This article is closed to any future comments.
excel school learn dashboards