Hide Row/Column Headers on Each Sheet

throtmorton

Board Regular
Joined
Aug 9, 2005
Messages
94
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm trying to make a simple macro to let the user hide row and column headers throughout the active workbook.

Here's what I came up with, and I've tried variations on the theme, but it only does the active sheet and won't cycle through to the others. Searched to no avail.

Any help appreciated.

Paul

Code:
Public Sub RowColumnHeaders()
Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
    ActiveWindow.DisplayHeadings = False
    
    Next ws

End Sub
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
throtmorton

Try this modification:

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> RowColumnHeaders()
<SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets
    ws.Activate
    ActiveWindow.DisplayHeadings = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ws
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
Not a code person, but I got this from recording. I just grouped the sheets and then selected the option not to display column and row headers. The last bit ungrouped the sheets.
Code:
Sub Macro5()

    Sheets.Select
    ActiveWindow.DisplayHeadings = False
    ActiveSheet.Select
End Sub
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Hmm, I used your coding on a two-sheet workbook with a heading in the first three cells of each sheet, as well as a header in each sheet, ran the macro multple times, and couldn't detect any changes to any of the two worksheets. What am I doing wrong?
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395

ADVERTISEMENT

Hmm, I used your coding on a two-sheet workbook with a heading in the first three cells of each sheet, as well as a header in each sheet, ran the macro multple times, and couldn't detect any changes to any of the two worksheets. What am I doing wrong?

The headers refered to here are the A, B, C, ..... at the top of each column, and the 1, 2, 3..... beside each row. You can see the option by going to Tools>Options and then choose the View tab. The option is toward the bottom. HTH clear things up for you.
 

throtmorton

Board Regular
Joined
Aug 9, 2005
Messages
94
Office Version
  1. 365
Platform
  1. Windows
Peter,

Yes, that works, thanks.

Is there also a way to come back to the current sheet? Right now, it cycles through and ends up at the farthest tab on the right.

Thanks,

Paul
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328

ADVERTISEMENT

Assuming you mean the A,B,C... column headers and the 1,2,3...row numbers :-

Code:
Public Sub RowColumnHeaders()
Dim ws As Worksheet
Set ws = ActiveSheet
Sheets.Select
ActiveWindow.DisplayHeadings = False
ws.Select
End Sub
 

throtmorton

Board Regular
Joined
Aug 9, 2005
Messages
94
Office Version
  1. 365
Platform
  1. Windows
Boller,

Thanks, but I seem to run into trouble with that code. If I use it in "This Workbook," I get the message "400." If I place it in a Module, I get Runtime 1004 error, and "Method 'Select' of 'Sheets' Failed"

Paul
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
throtmorton:

Yes, I now see what you were describing, and I got the same problem as you, that is, only the active sheet would not display the A, B, C and 1, 2, 3 headings.

But, I see that Peter's good code did the trick!:)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
Peter,

Yes, that works, thanks.

Is there also a way to come back to the current sheet? Right now, it cycles through and ends up at the farthest tab on the right.

Thanks,

Paul
try this:

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> RowColumnHeaders()
<SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> CurrentSheet <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Set</SPAN> CurrentSheet = ActiveSheet
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets
    ws.Activate
    ActiveWindow.DisplayHeadings = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ws
    CurrentSheet.Activate
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Watch MrExcel Video

Forum statistics

Threads
1,113,992
Messages
5,545,373
Members
410,679
Latest member
rolandbianco
Top