How to Use Go To Special in Excel using VBA?

Go To special is Excel dialog box to use different kinds for work. We select all Comments, Constant, Formulas, Blank Cells, Current Region, Current Array, Objects, Conditional Cells, and Data Validation applied cells at a time from Go To Special after we can apply different kinds of action on these cells.

Shortcut Keys for Accessing Go To Special is Ctrl + G and after that Click Special Button (Alt + S). Then, we can access all types of feature in Go to Special.

We use Go To Special from Excel Dialog Box and VBA.

In this blog, we will use Go To Special from VBA code in Excel and given example below based on Go To Special options.

how to use go to special in excel

How to select all Comment Cells into Excel Sheet through VBA?

Sub Select_Comment()

    ‘Select all Comment in Excel Sheet

    Sheet1.UsedRange.SpecialCells(xlCellTypeComments).Select

    Selection.Interior.Color = vbGreen

End Sub

How to delete all Comment Cells into Excel Sheet through VBA?

Sub Delete_All_Comment()

Sheet1.UsedRange.SpecialCells(xlCellTypeComments).Select

Selection.ClearComments

End Sub

How to select all Constant in Excel Sheet through VBA?

Sub Select_Constant()

    ‘Select all Non-Blank Cell

    Sheet1.UsedRange.SpecialCells(xlCellTypeConstants).Select

    Selection.Interior.Color = vbGreen

End Sub

How to select all Formulated Cells in Excel Sheet through VBA?

Sub Select_Formula()

    ‘Select all Formulated Cells

    Sheet1.UsedRange.SpecialCells(xlCellTypeFormulas).Select

    Selection.Interior.Color = vbGreen

End Sub

How to select all blank Cells in Excel Sheet through VBA?

Sub Select_blank_Cells()

    ‘Select Blank Cell

    Selection.SpecialCells(xlCellTypeBlanks).Select

    Selection.Interior.Color = vbYellow

End Sub

How to select all Validation Cells in Excel Sheet through VBA?

Sub Select_Validation_Cells()

    ‘select all used data vaalidation cells

    Sheet1.UsedRange.SpecialCells(xlCellTypeAllValidation).Select

    Selection.Interior.Color = vbGreen

End Sub

How to select all Visible Cells in Excel Sheet through VBA?

Sub Select_Visible_Cells()

    ‘use this code when you apply filter

    Sheet1.Range(“a1”).CurrentRegion.SpecialCells(xlCellTypeVisible).Select

    Selection.Interior.Color = vbGreen

End Sub

How to select all Shapes in Excel Sheet through VBA?

Private Sub CommandButton7_Click()

        Sheet1.Shapes.SelectAll

End Sub

How to select all Array in Excel Sheet through VBA?

Private Sub CommandButton8_Click()

    ‘where apply array formula

    Sheet1.Range(“h17”).CurrentArray.Select

    Selection.Interior.Color = vbRed

End Sub

How to Use Go To Special in Excel using VBA?

Download File

How to use Paste Special in Excel using VBA?

Leave a Reply

Your email address will not be published. Required fields are marked *