By default, the Gridlines are shown as gray lines in an Excel file. Those lines help us better visualize the data. However, we may want to disable the Gridlines sometimes, and if we have a lot of sheets in our workbook, then it will take a lot of time to do that. So, to do it faster, we can apply Excel VBA Macros to it. In this article, we will show you 3 quick ways to remove Gridlines in Excel using VBA.
Download Practice Workbook
3 Handy Approaches to Remove Gridlines in Excel Using VBA
To demonstrate our methods, we have selected a dataset with 3 columns: “Name“, “Gender”, and “City“. This dataset represents data for six employees for a particular retail company. Our aim is to formulate VBA Macro codes to remove Gridlines in Excel.
Before jumping on to the step-by-step guides, you need to make sure that the Developer tab is turned on in the Ribbon. If it is not turned on, then you can turn it on simply by following these steps:
- Firstly, File → Options → Customize Ribbon tab → select Developer.
- Then, press OK.
1. Removing Gridlines from Active Worksheet Using VBA
For the first method, we will remove Gridlines from the active Sheet using VBA.
- First, we bring up the VBA Module window, where we type our codes.
- To do this, from the Developer tab → select Visual Basic. Alternatively, you can press ALT+F11 to bring up this too.
- So, the VBA window will pop up.
- Next, from the Insert tab, select Module.
- Here, we insert VBA code to remove Gridlines in Excel.
- After that, type the following code inside the VBA Module window.
Sub Remove_Gridlines_Active_Sheet()
ActiveWindow.DisplayGridlines = False
End Sub
VBA Code Breakdown
- First, we are calling our Sub procedure Remove_Gridlines_Active_Sheet.
- Then, we set the DisplayGridlines property to false.
- Thus, this code works to remove Gridlines in Excel.
- So, our code will execute and it will remove Gridlines from the active Excel Sheet.
Read More: How to Remove Gridlines in Excel When Printing (6 Easy Ways)
2. Using VBA to Remove Gridlines from Specific Worksheet
In this section, we will hide Gridlines from a specific Sheet named “sSheet” in Excel.
Steps:
- Firstly, as shown in the first method, bring up the Module window.
- Secondly, type the following code inside that.
Sub Hide_Gridlines_Specific_Sheet()
Dim xSheet As Worksheet
Set xSheet = Worksheets("sSheet")
'Gridlines in "sSheet" will be hidden
xSheet.Activate
ActiveWindow.DisplayGridlines = False
End Sub
VBA Code Breakdown
- First, we are calling our Sub procedure Hide_Gridlines_Specific_Sheet.
- Then, we declare the variable types.
- After that, we define the specific Sheet.
- Then, the code activates that Sheet and set the DisplayGridlines property as false to hide Gridlines in Excel.
- Thus, this code works to achieve our goal.
- Then, as shown in method 1, we Save and Run this Module.
- Thus, this code will remove Gridlines in Excel.
Read More: How to Remove Gridlines in Excel for Specific Cells (2 Quick Methods)
3. Applying VBA to Remove Gridlines in Excel Workbook
In the last method, we will re-enable the Gridlines in all Sheets, and then using a VBA code, we will remove the Gridlines from the Workbook using a For Each Next Loop.
Steps:
- Firstly, from the View tab → select Gridlines for all the Sheets. This will re-enable Gridlines in the Workbook.
- Secondly, as shown in the first method, bring up the Module window.
- Next, type the following code inside that.
Sub Hide_Gridlines_Workbook()
Dim xSheet As Worksheet
'Gridlines in Workbook will be hidden
For Each xSheet In Worksheets
xSheet.Activate
ActiveWindow.DisplayGridlines = False
Next xSheet
End Sub
VBA Code Breakdown
- First, we are calling our Sub procedure Hide_Gridlines_Workbook.
- Then, we declare the variable type.
- After that, the code uses a For Each Next loop to go through all the Sheets of the Workbook.
- Then, the code activates all Sheets inside the Workbook and set the DisplayGridlines property as false to hide Gridlines in Excel.
- Thus, this code works to achieve our goal.
- Then, as shown in method 1, we Save and Run this Module.
- Thus, this code will remove Gridlines in the Excel Workbook using VBA.
Read More: How to Remove Gridlines from Table in Excel (2 Examples)
Practice Section
We have re-enabled the Gridlines in the Excel file before uploading. You can execute the codes and follow along with this article.
Conclusion
We have shown you 3 quick ways to remove Gridlines in Excel using VBA. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!