How to turn off gridlines in Excel using VBA

For me, ditching the gridlines in Excel is a no-brainer. It instantly cleans up the visual landscape, especially on a screen. If you’ve ever spent a whole day, week, years, reading Excel spreadsheets you’ll agree those gridlines can become a real eyesore. And when you turn them off, it’s a miracle… everything is so much easier to spot. A gridless spreadsheet is simply easier to digest and navigate. It’s a small tweak but with a big impact, making your work not only more functional, but also more visually appealing.

Most of us know how to turn gridlines off manually, using the Excel menus: head to the View tab, locate the “Show” group, and then uncheck the “Gridlines” checkbox. Done. You’ve got a nice, clean spreadsheet for reading on the screen.

But, it’s also nice to be able to turn off gridlines in your VBA programs. I have several “formatting” routines for my spreadsheets, in which one of the final steps to get that nice and polished look, is turning off gridlines. And I sure as heck aren’t going to be turning them off manually after I run my VBA routine.

Problem is, how to do it? If you ask your favorite AI for code, you’ll probably get something like this:

ActiveSheet.DisplayGridlines = False

And if you run it, Runtime Error: Object doesn’t support this property or method. Seems our favorite AI is using outdated coding methods. Oops.

How to turn off gridlines using VBA, in 2024

After much searching through the Microsoft VBA reference for Excel, turns out the error message is right. The Worksheet object has no “DisplayGridlines” property or method. So the code above given by AI, is wrong.

You’d think the gridlines would be part of the Worksheet object, but no. Gridlines in current versions of VBA are a bit more unconventional. They actually belong to the Window object in Excel, as a property. The Window object represents a window that contains an Excel spreadsheet. To reference it, you can use either a numeric index or a name reference.

So in our case, to turn off the gridlines, we’d have to use the Window object. For example to turn them off in the active window:

Windows(1).DisplayGridlines = False

Or if I want to use a name reference:

Windows("myexcelfile.xlsx":1).DisplayGridlines = False

The :1 after the file name means the first instance of the window. Remember that in Excel, you can have many windows displaying the same file.

But, what if my filename changes? What if the window isn’t the first one in line? What if I want something more universal for turning off gridlines?

Turns out there’s an ActiveWindow object in Excel VBA, which references, you guessed it. So all we have to do is employ this object in our code, to turn off the gridlines on whatever window is currently in use:

ActiveWindow.DisplayGridlines = False

And, goodbye gridlines.

Turning on gridlines is also easy, just change the False above to a True.

And for my final trick, what if you want to toggle the gridlines? If they’re off, turn on, and viceversa? Since we’re dealing with a boolean property, we can NOT it to do a toggle:

ActiveWindow.DisplayGridlines = not ActiveWindow.DisplayGridlines