How to Remove Gridlines in Excel Using VBA

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Remove Gridlines in Excel Using VBA 1

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.

How to Remove Gridlines in Excel Using VBA 2


1. Removing Gridlines from Active Worksheet Using VBA

For the first method, we will remove Gridlines from the active Sheet using VBA.

Steps:

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

How to Remove Gridlines in Excel Using VBA 3

  • So, the VBA window will pop up.
  • Next, from the Insert tab, select Module.
  • Here, we insert VBA code to remove Gridlines in Excel.

How to Remove Gridlines in Excel Using VBA 4

  • After that, type the following code inside the VBA Module window.
Sub Remove_Gridlines_Active_Sheet()
    
    ActiveWindow.DisplayGridlines = False
    
End Sub

How to Remove Gridlines in Excel Using VBA 5

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.
  • Afterward, Save the Module.
  • Then, put the cursor inside the Sub procedure and press Run.

How to Remove Gridlines in Excel Using VBA 6

  • So, our code will execute and it will remove Gridlines from the active Excel Sheet.

How to Remove Gridlines in Excel Using VBA 7

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:

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 2

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.

VBA Code 2 Output

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.

Turn On Gridlines

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

How to Remove Gridlines in Excel Using VBA

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.

Workbook Code

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.

How to Remove Gridlines in Excel Using VBA 1


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!


Related Articles

Rafiul Haq

Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo