How to Select Visible Cells in Excel with VBA (5 Easy Methods)

If you are looking for special tricks to select visible cells using VBA in Excel, you’ve come to the right place. There are numerous ways to select visible cells using VBA in Excel. This article will discuss the details of these methods to select visible cells using VBA in Excel. Let’s follow the complete guide to learn all of this.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.


5 Easy Methods to Select Visible Cells in Excel with VBA

In the following section, we will use five effective and tricky methods to select visible cells using VBA in Excel. In this tutorial, we’ll cover five different VBA codes: using the UsedRange property, the Find method, selecting cells after auto filtering, and selecting cells after manual filtering.  This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Select Only Visible Cells

If you want to select only visible cells using the VBA code in Excel, you need to use the help of VBA. This is Microsoft’s event-driven programming language called Visual Basic for Applications (VBA). To use this feature you first need to have the Developer tab showing on your ribbon. Click here to see how you can show the Developer tab on your ribbon. You can then use this detailed Excel VBA code to select only visible cells.

Select Visible Cells in Excel with VBA

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

open window of VBA

⧭ Insert Module:

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

  • As a result, a new module will be created.

⧭ Insert VBA Code:

  • Now select the module if it isn’t already selected. Then write down the following code in it.
Sub select_visible_cells()
    Range("B4:C9").Select
    Range("B5").Activate
    Selection.SpecialCells(xlCellTypeVisible).Select
End Sub
  • Next, save the code.

⧭ Run VBA Code:

  • Afterward, close the Visual Basic window. After that press Alt+F8.
  • When the Macro dialogue box opens, select the following macro in the Macro name. Click on Run.

Run VBA code

⧭ Output:

  • Therefore, this will select only the visible cells as shown below.

🔎 VBA Code Explanation:

Sub select_visible_cells()

First of all, provide a name for the sub-procedure of the macro.

Range("B4:C9").Select
    Range("B5").Activate
    Selection.SpecialCells(xlCellTypeVisible).Select

This piece of code will select the specified range of visible cells.

End Sub

Finally, end the sub-procedure of the macro.

Read More: [Fixed!] Paste Visible Cells Only Not Working (4 Possible Solutions)


2. Select Visible Cells Using UsedRange Property

Here, we will demonstrate another VBA method to select visible cells in Excel. We will use the VBA usedrange property to do the task. Let’s walk through the following detailed steps to select only visible cells using the VBA code in Excel.

using UsedRange property to Select Visible Cells in Excel with VBA

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

⧭ Insert Module:

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

insert module

  • As a result, a new module will be created.

⧭ Insert VBA Code:

  • Now select the module if it isn’t already selected. Then write down the following code in it.
Sub Select_Range()
    Dim r1ng As Range
    Set r1ng = Sheets("Sheet1").UsedRange
    r1ng.Select
End Sub
  • Next, save the code.

⧭ Run VBA Code:

  • Afterward, close the Visual Basic window. After that press Alt+F8.
  • When the Macro dialogue box opens, select the following macro in the Macro name. Click on Run.

⧭ Output:

  • Therefore, this will select only the visible cells as shown below.

Select Visible Cells using UsedRange property in Excel with VBA

🔎 VBA Code Explanation:

Sub Select_Range()

First of all, provide a name for the sub-procedure of the macro.

Dim r1ng As Range

Next, declare the necessary variable for the macro.

Set r1ng = Sheets("Sheet1").UsedRange
  r1ng.Select

It returns the range used on the specified worksheet as a Range object.

End Sub

Finally, end the sub-procedure of the macro.


Similar Readings


3. Utilizing VBA Find Method

In the third method, we will use the Find method to select a particular visible cell in Excel. Follow these steps to see how we can use the code and the breakdown of it.

apply find method to Select Visible Cells in Excel with VBA

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

⧭ Insert Module:

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

insert module

  • As a result, a new module will be created.

⧭ Insert VBA Code:

  • Now select the module if it isn’t already selected. Then write down the following code in it.
Sub Found_Range()
    Dim r1ng As Range
    Set r1ng = Sheets("Sheet1").UsedRange.Find("Body")
      r1ng.CurrentRegion.Select
End Sub
  • Next, save the code.

⧭ Run VBA Code:

  • Afterward, close the Visual Basic window. After that press Alt+F8.
  • When the Macro dialogue box opens, select the following macro in the Macro name. Click on Run.

⧭ Output:

  • Therefore, this will select only the visible cells as shown below.

Select Visible Cells in Excel with Find method of VBA

🔎 VBA Code Explanation:

Sub Found_Range()

First of all, provide a name for the sub-procedure of the macro.

Dim r1ng As Range

Next, declare the necessary variable for the macro.

  Set r1ng = Sheets("Sheet1").UsedRange.Find("Body")
r1ng.CurrentRegion.Select

It returns the range used on the specified worksheet as a range object, and finally, by using the find method, we will get the specified range.

End Sub

Finally, end the sub-procedure of the macro.

Read More: How to Copy Visible Cells Only without Header Using VBA


4. Select Visible Cells After Auto Filter

In this fourth method, we will use a VBA code that is constructed such that the filter requirements are already embedded in the code. When you need to filter a specific column by a specific parameter every time, this is especially useful. The process will be faster if you want to repeat it over and over again since you won’t have to select parameters each time. For an overview of the code and the breakdown, follow these steps.

Select Visible Cells in Excel with VBA after auto filter

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

⧭ Insert Module:

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

insert module

  • As a result, a new module will be created.

⧭ Insert VBA Code:

  • Now select the module if it isn’t already selected. Then write down the following code in it.
Sub Select_AutoFiltered_VisibleRows_NewSheet()
Dim FilterValues As String
FilterValues = "Texas"
ActiveSheet.Range("B4:E14").AutoFilter
ActiveSheet.Range("B4:E14").AutoFilter field:=2, Criteria1:=FilterValues
ActiveSheet.Range("B4:E14").SpecialCells(xlCellTypeVisible).Select
End Sub
  • Next, save the code.

⧭ Run VBA Code:

  • Afterward, close the Visual Basic window. After that press Alt+F8.
  • When the Macro dialogue box opens, select the following macro in the Macro name. Click on Run.

⧭ Output:

  • This is how the spreadsheet will look now. VBA code automatically auto filters and selects the visible cells in the Excel spreadsheet.

Select Visible Cells in Excel with VBA after auto filter

🔎 VBA Code Explanation:

Sub Select_AutoFiltered_VisibleRows_NewSheet()

First of all, provide a name for the sub-procedure of the macro.

Dim FilterValues As String

Next, this portion declares the variable. We have only one here- FilterValues.

FilterValues = "Texas"

We are entering a string value for the Filtervalues in this section. We will use this as the filter value later on. If you need to filter with different values put the value in here.

ActiveSheet.Range("B4:E14").AutoFilter
ActiveSheet.Range("B4:E14").AutoFilter field:=2, Criteria1:=FilterValues

We are now selecting the range B4:E14 and using the .autofilter method in it. By selecting the field and Criteria1 parameter we are choosing to filter the FilterValues value from the second column. You can change the field value to the serial of the column you want to filter from. Also, you can change your range if you have a different range.

ActiveSheet.Range("B4:E14").SpecialCells(xlCellTypeVisible).Select

After the filter is complete, this line of code simply selects them.

End Sub

Finally, end the sub-procedure of the macro.


5. Select Visible Cells After Filtering Manually

In this fifth method, we will illustrate another VBA code to select visible cells after manually filtering. First of all, we have to create a table and then we will filter the table, then finally we will use code to select visible cells. To do the task you have to follow the following process.

📌 Steps:

  • Firstly, select the range of cells as shown below.
  • Then, go to the Insert tab and select Table.

  • Therefore, the Create Table window will appear. Click on OK.

  • Therefore, a table will be created as shown below.

  • Next, we are going to filter the table. To do this, click on the dropdown menu, and select your desired option as shown below. Then, click OK.

  • Consequently, the dataset will look like this.

  • Now, we are going to use the VBA code to select the visible cells.

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

⧭ Insert Module:

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

insert module

  • As a result, a new module will be created.

⧭ Insert VBA Code:

  • Now select the module if it isn’t already selected. Then write down the following code in it.
Sub Manual_Selection()
   With Sheets("Filter").ListObjects("Table1")
      Intersect(.DataBodyRange.SpecialCells(xlVisible), .Parent.Range("B:E")).Select
   End With
End Sub
  • Next, save the code.

⧭ Run VBA Code:

  • Afterward, close the Visual Basic window. After that press Alt+F8.
  • When the Macro dialogue box opens, select the following macro in the Macro name. Click on Run.

run vba

⧭ Output:

  • This is how the spreadsheet will look now. The VBA code selects the visible cells in the Excel spreadsheet.

🔎 VBA Code Explanation:

Sub Manual_Selection()

First of all, provide a name for the sub-procedure of the macro.

With Sheets("Filter").ListObjects("Table1")

It will return a ListObjects collection from the Worksheet object by using the ListObjects property.

   Intersect(.DataBodyRange.SpecialCells(xlVisible), .Parent.Range("B:E")).Select
   End With

Afterward, this piece of code will select the specified range of visible cells.

End Sub

Finally, end the sub-procedure of the macro.

Read More: Excel VBA to Select First Visible Cell in Filtered Range


Conclusion

That’s the end of today’s session. Now, I strongly believe that from now, you may be able to select visible cells using VBA in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo