Hide / Unhide Gridlines in Excel – VBA

In this article, we will learn to hide and unhide the gridlines in Excel. We will learn to do this from the Excel ribbon and using the VBA programming language. By default, the gridlines option in the Excel ribbon can hide or unhide gridlines for the active worksheet.

Let us start learning now.

Hide or Unhide Gridlines From Excel Ribbon

Hiding Gridlines gives a clean look to the spreadsheet. We can show or hide the gridlines for the active worksheet from the Excel ribbon using the following steps.

• Go to the View tab on the Excel ribbon.
• Navigate to Show Group and mark/unmark the checkbox for Gridlines to hide/unhide the active worksheet.

Hide Gridlines for All Worksheets in Active Workbook

We can use the DisplayGridlines property of the Worksheet Object. We set the value of the DisplayGridlines property to False and this will hide the Gridlines of the Worksheet.

The Worksheets Collection contains a reference to all the worksheets in the Active Workbook. The For Each loop can be used to refer to each worksheet object referred to by the Worksheets Collection.

We will first activate the worksheet and then use ActiveWindow to hide the gridlines.

You can use the following Sub procedure to hide the gridlines in all the worksheets of the active workbook.

Sub hideGridlinesAllWorksheets()

'code to hide gridlines from all the worksheets of workbook
Dim ws As Worksheet

For Each ws In Worksheets
'activate the worksheet
ws.Activate
'hide the gridlines from activated window (worksheet)
ActiveWindow.DisplayGridlines = False
Next ws

End Sub

When you run the sub-procedure, it hides gridlines from all the worksheets.

Form (Project) To Select Worksheets To Hide Gridlines

In this section of the blog, we will create a UserForm in which we can select the worksheets from which we wish to remove gridlines. You can follow these steps to implement the working.

• Press Alt and F11 keys to open the VBE. Go to Insert tab and click on UserForm.

• Insert a ListBox and Command Button using the ToolBox in the UserForm.

• Press the F4v key to open the Properties Window (for changing properties of ListBox and Command Button)
• Select ListBox and then the command button on the user form and change the following properties from the Properties Window.

• Double Click on the user form in design window and paste the following code.

Private Sub CommandButton1_Click()
Dim i As Integer

'loop through all the worksheets in list box
For i = 0 To ListBox1.ListCount - 1
    
    'check if worksheet is selected in list box
    If ListBox1.Selected(i) Then
    '******************************************
    
    'activate the selected worksheet
    Worksheets(ListBox1.List(i)).Activate
    '******************************************
    
    'remove the gridlines of selected worksheet
    ActiveWindow.DisplayGridlines = False
    '******************************************
    End If
Next i

End Sub

Private Sub UserForm_Initialize()

'add the worksheet names to listbox
'when form is initialized

For Each ws In Worksheets
ListBox1.AddItem ws.Name
Next ws

End Sub

You can download this project from here.

Here we are taking the Command Button Button1 on the Spreadsheet and add the following code to call the UserForm1 when button is clicked.

Sub Button1_Click()
UserForm1.Show
End Sub

This brings us to an end.

Thank you for reading.❤