Help Adding a Command to Quick Access Toolbar

[Sundance_Kid]

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

 

[arpd123]

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.:

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):

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]

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