VBA - Work With Gridlines

Do you notice the faint lines around cells in any Excel sheet? These are called gridlines. They are used to distinguish cells on the worksheet i.e. each row and each column is separated by a gridline.

Gridlines are visible by default in Excel and you can hide them if you don’t want at any point of time. However, gridlines are sheet specific. That means you will have to hide them for each sheet or select multiple sheets and hide the gridlines for those sheets.

Also, if you are using Excel 2003 or earlier versions, finding the gridlines hide/unhide option is bit long. You will have to follow Tools > Options > View > Gridlines (Check/uncheck the box). It is relatively easy in Excel 2007 or later as this option is available directly under the View tab.

Today, we will see how to hide/unhide gridlines for all the sheets in a workbook through a macro. We will follow the same logic we followed in Unhide all hidden sheets post.

Record Hiding a Sheet’s gridline

Create a new workbook and save it as macro enabled workbook.

In Excel 2007 or later: Click on View ribbon > Macros drop-down button > Record Macro

In Excel 2003 or earlier: Click on Tools menu > Macro > Record New Macro

• Enter "Macro Name" as HideGrid
• Click OK.

After clicking OK, you can notice a blue coloured button in Excel 2007 or later and a word "Recording" on the status bar in Excel 2003 or earlier. Now your macro started recording.

Excel 2003 or earlier versions click on Tools > Options > View > Gridlines (uncheck the box to hide gridlines). Excel 2007 or later, click View tab > Show group > Gridlines (uncheck the box to hide gridlines). Your macro would have recorded your action, let’s go and check it out. Before that stop macro recording by clicking on that blue button.

Press Alt+F11. This should open up the VBE window. (Read about VBE appearance here). In Project Explorer window, you can notice that Excel created a new folder Modules with a plus sign next to it. Clicking on that sign or double clicking on folder name shows you the child item under that folder which is Module1. If you are unable to view Project Explorer window, access it from View menu of VBE window. My recorder shows the below code.

Sub HideGrid()
'
' HideGrid Macro
'
 '
    ActiveWindow.DisplayGridlines = False
End Sub

Notice the line ActiveWindow.DisplayGridlines = False. This is what we need to hide the gridlines for the sheets; on the contrary, setting the property to True will show the gridlines. Now all we need to do is apply this setting to each sheet in the workbook. At this point, you might want to take a look at Sheets collection and how we use a For Each statements to loop through all sheets in our earlier post.

Also in the above code, note that ActiveWindow is the sheet that is active at the point of running the macro. So instead of changing the sheet property, macro is actually accessing ActiveWindow property to change the visibility of gridline. That gives an important point to note for this post, you should have a sheet active (selected) to change its gridlines property.

So, logic for our code would be, for every sheet in this workbook, activate (select) the sheet and change the gridlines property to false. Let’s go ahead and do that in code format.

Sub HideAllGrids()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Sheets ‘For all sheets in workbook
    Sht.Select ‘Select sheet
    ActiveWindow.DisplayGridlines = False ‘change gridlines property to false
Next Sht ‘go to next sheet
End Sub

The above code uses a variable Sht to represent the sheet. We are activating each sheet in the workbook and changing the gridline property to False. This process repeats until the entire sheet’s gridlines are changed.

Show All Gridlines

In the above code, change the line ActiveWindow.DisplayGridlines = False to ActiveWindow.DisplayGridlines = True if you want to show the gridlines in all sheets.

Toggle Gridlines

In the above code, change the line ActiveWindow.DisplayGridlines = False to ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines if you want to toggle the gridlines in all sheets.

Hide Gridlines – Take 2

Code above will loop through each sheet to change the property. You can get the same result from the below code without use of a variable.

Sub HideAllGrids()
Sheets.Select
ActiveWindow.DisplayGridlines = True
End Sub

Hide Gridlines – Take 3

Above set of codes work perfectly when all the sheets are ready to be selected. I mean to say, if there is a hidden sheet, that sheet cannot be selected, isn’t it? So, above code will fail if there is at least one sheet that is hidden. What to do in that case?

Use On Error Resume Next

If you just want to ignore the hidden sheet, include the above error trapping in your code.

Sub HideAllGrids()
Dim Sht As Worksheet
On Error Resume Next
For Each Sht In ActiveWorkbook.Sheets ‘For all sheets in workbook
    Sht.Select ‘Select sheet
    ActiveWindow.DisplayGridlines = False ‘change gridlines property to false
Next Sht ‘go to next sheet
End Sub

Check the Visibility of Sheet

You can check the visibility property of the sheet and decide about the next step. For example, you might want to unhide the sheet and apply the property. Let’s see how to do that.

Sub HideAllGrids()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Sheets
    If Sht.Visible <> True Then Sht.Visible = True ‘Check if sheet is visible, if not make it visible
    Sht.Select
    ActiveWindow.DisplayGridlines = False
Next Sht
End Sub

That’s all. As we did above, you can modify your code to fit to any scenario. Just remember doing a little bit of research every time, doesn’t matter if you fail at the beginning, you will surely master it quickly. Happy Learning!!