Odd Formatting Issue

[SeveralTradesLater]

Hi everyone,

I have a bit of a novel formatting issue. I inherited a workbook where a bunch of cell ranges were named in the name manager.

However, for some of those named ranges their "names" are "labeled" all over those cells. Example "Products" is I guess "semi translucently" covering cells in the middle of a spreadsheet, it doesn't impact the values at all but just doesn't look aesthetically pleasing.

Can someone please kindly advise on how I can remove this odd "formatting"?

Thank you.

 

[Fluff]

Try changing the zoom level nearer to 100%

 

[SeveralTradesLater]

You were absolutely right. It disappeared. Why does this happen? and can it be permanently removed without zooming? Thank you.

 

[Fluff]

No idea why it happens & don't think it can be stopped.

 

[Alex Blakenburg]

This should only happen when you zoom to 39% or less. Do you really need everything to be that small

Tom’s Tutorials For Excel: Showing Your Named Ranges At 39% Zoom – Tom Urtis

www.atlaspm.com

 

[Peter_SSs]

can it be permanently removed without zooming?

Try this. Change the False to True and run it again if you want to bring the names back for zoom <40

Sub Hide_Named_Ranges()
  Dim nm As Name
 
  For Each nm In Names
    nm.Visible = False 'True
  Next nm
  Application.ScreenUpdating = True
End Sub

 

[Alex Blakenburg]

@Peter_SSs not sure that wouldn’t create more issues than it solves. eg when you reverse it and run as true you ideally want to only make the previously visible ones visible again. It might be enough to exclude anything with a prefix of _xlfn (I think I have seen a variation of this and just _xl might be safer)

 

[Peter_SSs]

when you reverse it and run as true

Possibly not actually going to happen given ..

can it be permanently removed

Anyway, it was something the OP could consider - as is your point.

 

[SeveralTradesLater]

Try this. Change the False to True and run it again if you want to bring the names back for zoom <40

Sub Hide_Named_Ranges()
  Dim nm As Name
 
  For Each nm In Names
    nm.Visible = False 'True
  Next nm
  Application.ScreenUpdating = True
End Sub

Thank you for the code, but sadly I'm not sure if I'm using it correctly.

My navigation:
1) Click Developer
2) Click Visual Basic
3) Sheet5(ProductsArchive), Double click, Paste code.
4) Floppy Disk
5) Play Button (Run sub/UserForm)
6) File Close and Return to Excel

But the tables are still visible below a 40% zoom. Please kindly advise. Much appreciated.
My intention is to be able to share to workbook with other users and not have them see the clutter of all those tables labeled. It's a fairly massive workbook.

 

[Peter_SSs]

Sorry, I have been away for a few days.
You have pasted in the wrong place.

Test with a copy of your workbook

Go back into the Visual Basic window & double click Sheet5 again.
Select the code you pasted there and Cut
On the menu in the vba window choose Insert -> Module
Paste the Cut code into the new right hand pane that opened.
Save and close the vb window.
Ensure the relevant sheet (ProductsArchive) is the active sheet
On the Developer ribbon tab click Macros, select the Hide_Named_Ranges macro & click Run