MsgBox "Select more than 1 cell.", vbExclamation, "Select more cells."Īpplication.Dialogs(xlDialogSelectSpecial).Show
Or you can display the Goto > Special… dialog box (using a macro) with just 1 click of the mouse or 2 keystrokes (if you pin it on the QAT) ! This takes one keystroke and a mouse-click or 3 keystrokes (if you don’t use the mouse) ? Of course, you can do it the way it was designed in Excel – press F5 to display the GoTo dialog box, and click on the Special… button. Quite often I find myself needing to use the GoTo Special command. Sub ShowHidePageBreaks()Īs the name suggests, this macro will show or hide the display of page breaks on the active sheet. This macro toggles the display of page-breaks on the active sheet. This macro toggles the display of zero-value cells on the active sheet.ĪctiveWindow.DisplayZeros = Not ActiveWindow.DisplayZerosĤ: Show or Hide page-breaks in active sheet Selection.AutoFilter Field:=lField, Criteria1:="" & ActiveCell.Value If TypeName(Selection) "Range" Then Exit Sub The macro itself is a fairly simple one-line command : This is another handy macro, which filters the current column based on the value of the active cell, except that the filter is applied as “show records NOT equal to the value of the active cell” 2: Filter on value NOT equal to ActiveCell value MsgBox "No cells found with this cell's contents"Īs you will notice, the macro checks whether the selection is 1 cell or multiple cells, and accordingly executes the Cells.Find command. MatchCase:=False, SearchFormat:=False).Activate LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ (What:=ActiveCell.Value, After:=ActiveCell, LookIn:=xlValues, _ This is a very useful macro which helps to search for the value in the ActiveCell within the selected range or the whole worksheet (if only ActiveCell is selected). Hopefully I will get a chance to post some more if this post is found to be good enough ?ġ: Find the value of ActiveCell within selection, or in the whole sheet This is the first bunch of macros which I use most frequently. And you can easily port it to any other computers that you use – or even share it with your friends and allied spooks. Like nuclear war, It’s a one-time exercise.
#Chip pearson excel keyboard shortcuts how to#
You can read more about how to set up a Personal Macro Workbook, in this excellent tutorial on Ron de Bruin’s website. This post is about some of the stuff that I have put in my Personal Macro Workbook over the years. There are many ways one can do this in Excel, but among the more effective and scalable ones, is storing commonly used macros in your Personal Macro Workbook. In fact, this is a topic that I think about in everything to do with computers. One of my favorite topics in Excel is – “How can I make my day-to-day tasks in Excel easier and faster ?”. I have been using spreadsheets since 1990, and Excel since 1995 – which sort of makes me a veteran in this sphere of business applications ? Hello, this is my first guest post on (or any Excel website for that matter), and I will try to keep it simple, but useful for our readers. So without further ado, please put your hands together and give a warm Chandoo welcome to secret agent KV. As discussed then, I ‘volunteered’ KV under pain of exposure to empty the contents of his secret satchel onto the virtual table, so that we can rummage through it. All you need to know is how to Google, Cut, and Paste.
#Chip pearson excel keyboard shortcuts code#
Yesterday, I talked about how you don’t have to know how to code in order to highly leverage VBA.