Help Adding a Command to Quick Access Toolbar

Sundance_Kid

New Member
Joined
Sep 2, 2017
Messages
32
Hi Folks,

I am looking to add the below command in bold and underlined to the Quick Access Toolbar.

I am able to add the Go To Special command to the toolbar but I would rather just add the Blank Cells command so as to reduce a click of the mouse.

However when I look in the list of commands in All Commands - i only see an option for Blank Query or Blank Rows.

Any ideas how I can find it and add it?

Find & Select
Go To Special
Blank Cells


Thank you
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

arpd123

New Member
Joined
May 14, 2018
Messages
24
Office Version
  1. 365
Platform
  1. Windows
I can't see how to do this through the application options.

However, you can do this by a macro (I am assuming you do not know how to use macros with the following instructions):
  • Enable the Developer tab on the Ribbon through the options if you have not already (It's under 'Customize Ribbon' and 'Popular Commands' in 'Choose Commands From'.
  • Click 'Record a Macro' in the Developer tab of the Ribbon and in the macro options prompt that appears select 'Personal Macro Workbook' under the 'Store Macro In' field. The macro can be something elementary, like simply clicking a cell, or even using the Go To Special command to blank cells.
  • Click 'Stop Recording' in the same location as the record button.
  • Open the VBA editor by clicking the 'Visual Basic' button in the Developer tab.
  • Go to the macro you have just recorded by looking in the Project viewer in the left pain and expanding the tree for your Personal Macro Workbook file. Expand the 'Modules' folder and select 'Module1' which should contain your macro.
  • There should be something that looks something like this in the code window on the right of the screen.:
VBA Code:
Sub Macro1)
'
' Macro1 Macro
'

'
    Range("F4").Select
End Sub

  • Edit it so that it looks like this instead, which I believe is the command you want (I am not sure what the utility of it is though, and have never used it before, so you can judge if it does exactly what you want it to):
VBA Code:
Sub Macro1()
    Selection.SpecialCells(xlCellTypeBlanks).Select
End Sub
  • Save the Personal Macro Workbook in the editor by clicking the disk icon in the upper left and go back to the Excel application options.
  • Go to 'Customize Quick Access Toolbar' and select the Quick Access Toolbar option.
  • Select 'Macros' under 'Choose Commands From' and select the macro that you have recorded.
  • From here you can customize the icon by clicking 'Modify' on the right when the macro has moved into the right pane.
 

Sundance_Kid

New Member
Joined
Sep 2, 2017
Messages
32
Hi, thank you very much for your detailed reply.

I have no experience with creating macros and I was hoping it was possible to find the blank cells option but obviously not.

I may look into your suggestion.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,167
Members
410,775
Latest member
alal1030
Top