Hide Columns hides but then reappears

[rplohocky]

Hello,
I have a strange one for ya. I highlight the columns that I want to hide then right click and select Hide but when i click on ANY cell in the sheet they reappear. I've tried 1 column, 2, 3 and more, same result. I have 5 sheets in this workbook and they are all the same. It has macros in it and I have been able to hide in the past so this is new.

 

[rplohocky]

Thank you all for all the help! I looks like this code works so far.

Sub AutoFitVisibleColumnsOnly()
  Dim c As Range
 
  Application.ScreenUpdating = False
  For Each c In ActiveSheet.UsedRange.EntireColumn.Columns
    If Not c.Hidden Then c.AutoFit
  Next c
  Application.ScreenUpdating = True
End Sub

 

[rplohocky]

Well... I take that last statement back. I just tried it again and now it doesn't auto fit. Not sure what changed but it no longer works.

 

[Peter_SSs]

Do you have any data in the worksheet? Note that the code only works on columns in the "used range".

If you have a visible column with data that is not AutoFitting as you might expect, does that column have any cells formatted with "Wrap Text"? If so, any column width for such cells "fits" since the data does fit within the cells.

 

[rplohocky]

Yes i do have data in most of the columns. I don't see a "used range" in the formula, am i missing something? I have a the cells set as accounting without any other formatting. I'm not sure why it stopped working but i can assure you other than clicking save and adding more number data to cells, i haven't changed anything.

 

[Peter_SSs]

I don't see a "used range" in the formula, am i missing something?

It seems so ..

Sub AutoFitVisibleColumnsOnly()
  Dim c As Range
  
  Application.ScreenUpdating = False
  For Each c In ActiveSheet.UsedRange.EntireColumn.Columns
    If Not c.Hidden Then c.AutoFit
  Next c
  Application.ScreenUpdating = True
End Sub

Are you able to upload a copy of the workbook (any sensitive data disguised or removed) to say DropBox or OneDrive or Google Drive etc and provide a publicly shared link here so that we could take a look to investigate further?
In all of my tests the code works as advertised (taking into consideration my comments about 'Wrap Text' cells) so there must be something different about your workbook.

 

[rplohocky]

Ok I see it now, not sure how I didn't before (used range)! After more testing here's what I found. I have a sheet that has columns where I insert expenses into various rows, then the bottom row has an ongoing balance like a checking account balance, so that number goes up or down based on a deduction or a deposit. When I enter a large number into the deposit area the columns need to expand to support the larger balance. What I see happening is I insert the large number and that column expands but all the columns to the right do not. They only expand when something is entered into one of those columns.

 

[Peter_SSs]

What I see happening is I insert the large number and that column expands but all the columns to the right do not.

Sounds a bit like you are triggering the code with a Worksheet_Change event code? If so, could you post the whole code(s)?
If not, how are you triggering the code after entering a number?

Also, If a big number is entered in a column, why do the the columns to the right need to expand?

 

[rplohocky]

Here is the code but i think i got it from you:

Sub AutoFitVisibleColumnsOnly()
  Dim c As Range
 
  Application.ScreenUpdating = False
  For Each c In ActiveSheet.UsedRange.EntireColumn.Columns
    If Not c.Hidden Then c.AutoFit
  Next c
  Application.ScreenUpdating = True
End Sub

The code is triggered automatically every time i enter any data in any of the cells, i enter data then hit enter and the column auto sets based on the length of the number.
Also the reason they need to expand is the bottom row shows the ongoing balance of the checking account so it goes up and down based on deposits and withdrawals.

This issue isn't that big of issue if we can't solve it and i don't want to keep bothering you on it if it just can't be done.

 

[Peter_SSs]

if it just can't be done.

It can be done, and you are not bothering me.

The code is triggered automatically every time i enter any data in any of the cells,

The above code by itself is not triggered automatically when you enter anything. To run, it either needs to be run manually or by some other code that does run automatically when you enter data.

Do you have any other vba code in the workbook?

If not, right-click the worksheet's name tab and choose View Code then paste the code below into the main right-hand pane that opens then again try entering something in the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
  AutoFitVisibleColumnsOnly
End Sub

 

[rplohocky]

No this is the only macro. I tried your ne macro but i get an error, i think its missing a decimal or 2. Also yes, i right clicked on the tab and inserted it into the window that opens for the page.