Hide Columns based on value in each column in a specific Row

gootly

New Member
Joined
Jan 31, 2022
Messages
10
Office Version
  1. 2021
Platform
  1. Windows
I have a Excel 2021 on Windows spreadsheet with many columns and rows

I need a macro that can look at every column from B to SL and hide that column if the value of row 1790 is below 200%. For complete disclosure row 1790 is a calculation of two other elements in the column divided by the other and the result is displayed as a percentage
Example =B1783/B1279

or simply show columns where row value of cell 1790 in that column is above 200%

I am open to suggestions. I have researched for days but not found a solution that applies.

Thanks in advance.

Mark
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
:unsure:
You mean from column B to column SL, hide a column where the value in that column in row 1790 is less than 200%?
If B:1790 < 200%, hide B.
However, this
show columns where row value of cell 1790 in that column is above 200%
implies that the column might already be hidden. This could be the case if underlying inputs (values) can be edited and the percentage rises above 200. It was hidden but now should not be?
 
Upvote 0
Hi & welcome to MrExcel.
How about
VBA Code:
Sub gootly()
   Dim Cl As Range
   Application.ScreenUpdating = False
   For Each Cl In Range("B1790:SL1790")
      Cl.EntireColumn.Hidden = Cl.Value < 2
   Next Cl
End Sub
 
Upvote 0
Hi & welcome to MrExcel.
How about
VBA Code:
Sub gootly()
   Dim Cl As Range
   Application.ScreenUpdating = False
   For Each Cl In Range("B1790:SL1790")
      Cl.EntireColumn.Hidden = Cl.Value < 2
   Next Cl
End Sub


I tried your code and got this error:

Run-time error '13'

Type mismatch

Cl.EntireColumn.Hidden = Cl.Value < 2

The above line was in yellow.

Mark
 
Upvote 0
Do you have any errors values (like #N/A, #CALC,#VALUE etc) on that row?
 
Upvote 0
:unsure:
You mean from column B to column SL, hide a column where the value in that column in row 1790 is less than 200%?
If B:1790 < 200%, hide B.
However, this
show columns where row value of cell 1790 in that column is above 200%
implies that the column might already be hidden. This could be the case if underlying inputs (values) can be edited and the percentage rises above 200. It was hidden but now should not be?

NO I mean greater than 200% not less.

I put the objective in two methods and tired to use the second one to simplify the process and you are assuming I am using both.

Mark
 
Upvote 0
Do you have any errors values (like #N/A, #CALC,#VALUE etc) on that row?

Fluff,

Yes I do. I have some #DIV/0! in a few columns. I apologize for not realizing that in the fields.

Mark
 
Upvote 0
Ok, should those columns be hidden?
 
Upvote 0
NO I mean greater than 200% not less.

I put the objective in two methods and tired to use the second one to simplify the process and you are assuming I am using both.
Well, 'scuse me for being stupid.
Good luck.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top