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?
Hi,
A. Numberformat that i've linked to a button.
.
B. Change comments in something looking more attractive => Also with a personal button
.
Looking forward form more timesavers….
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!
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
I think this shorter macro does the same thing as your posted CopyPasteValues macro…