Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Vba Macro To Hide Certain Columns

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hey

I would like to use a VBA Code to hide columns that fall under a certain criteria. For example, lets assume I have several columns of data with a "total" cell under each column that sums the numbers. What I would like to do is hit a macro button that will automatically hide any column that has a sum less than a certain number. This would have to be dynamic meaning we would have to type a number into a cell in excel; for example if I wanted to hide all columns that have a sum less than 5%, I would like to enter 5% into a cell, hit a macro, and then have it go through and hide the columns.

Hope this isnt too confusing. Let me know if you need more clarification.

Best,
SQ

View Answers     

Similar Excel Tutorials

Hide Data Within a Worksheet in Excel
In Excel you can actually hide data that is stored within a worksheet. This allows you to show data that is useful ...
Hide or Unhide a Worksheet by Hand in Excel
I'll show you how to hide worksheets in Excel so that a user cannot see them but you can still access data on them ...
Completely Hide the Ribbon Menu in Excel
I'll show you how to completely remove the entire Ribbon Menu from Excel. This will allow you to have a streamline ...
Run a Macro from Another Macro in Excel
I will show you how to run a macro from another macro in Excel.  This means that you can run any macro when you ne ...

Helpful Excel Macros

Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Hide Specific Comments in Excel - Comments Will Still Display on Hover
- Hide specific comments in Excel with this macro. Comments are still visible on hovering over the cell that contains the
Hide Comments in Excel Completely - Even Indicators Will not Appear
- Hide all comments in an Excel workbook. No indicators will be displayed and comments will not appear when you hover ove
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Hide Comments in Excel Partially - Comment Indicator Shows and Will Display on Hover
- Hide comments in Excel with this macro. Comment indicators will still appear in the cells and users will also be able to

Similar Topics







I'm trying to write a Macro that will hide columns based on a user input, and then rows based on what is left over.

Basically, when the workbook is opened, I want a box to pop up that says enter store number. Then the store number is typed, and OK is pressed, and it will hide all columns in the range Z:IV that do not have that store number in row 1. There will only and always be one column that will remain unhidden.

After that is done, I want to hide all rows in the range 2:2000 that have a blank or 0 value in that column that was NOT hidden in the above step. This column will be dynamic though.

IE: If you enter store "101" in, all columns right of Y will hide except column AA (just an example), which contains "101" in cell AA2. Then all rows that are blank or have a value of 0 in column AA will be hidden.

Then if you enter store "202" in, all columns right of column Y will hide except column BA this time, which contains "202" in cell BA2. Then all rows that are blank or have a value of 0 in column BA will be hidden as well.

I would like this to all happen in one macro. So when the store # is entered, everything is done and ready to view.

I know this is probably difficult, but if it makes it much easier, I guess I could deal with deleting rows and columns instead of hiding. I would much rather hide, but if deleting is the only way, it is acceptable.

Thanks in advance.
-Matt


Hi,
I have excel sheet which has A to D Columns as text coulmns. E to IV are data columns. I need to create hide and unhide buttons so that :
Clicking on HIDE, hides 7 columns starting from E. When user clicks on HIDE again it should hide next 7 columns and so on as so forth. Clicking on UNHIDE button should unhide last 7 hidden columns and so on and so forth. e.g. A user clicks on HIDE button once, 7 columns from E should hide, next click on HIDE should hide next 7 columns from L, next click on HIDE should hide next 7 columns from S. Now user clicks on UNHIDE button, it should unhide last hidden columns i.e. show all columns until S.

Any help is greatly appreciated.

Thanks,
Prakash


Hello all,

I am using Excel 2007 in Windows 7. My issue is that I need columns to automatically hide themselves without having to Alt+F8 and running the program. My data set is:


A B C D E F 1 1981 1982 1983 1984 1985 1986 2 4 2 1 3 6 3 3 5 3 2 7 9 4 4 6 5 2 5 6 6
If the value in Row 1 equals 0, I want that entire column to automatically hide itself. So if D1 = 0, then Column D would automatically hide itself. Any suggestions?


Hi -

Could someone please show me a better way to hide all columns with zero value in my excel 2003 file? It would be great that i could have a macro embed in my workbook/sheet that automatically hide all these zero value columns and only show the ones with valid values. Currently, i have to mananually select all columns with zero value and hide them one by one. This takes so much time since i have more than 6-7k rows. Thanks!

--Tony


Hello Everyone,

I am trying to figure out a macro (or formula if one exists) to hide/unhide columns in my excel worksheet based on the cell value in the first row of the column. Based on how my sheet is set up, this is my desired result:

If cell C1:=0, then Hide Column C. If not, unhide column C
If cell D1:=0, then Hide Column D. If not, unhide column D
If cell E1:=0, then Hide Column E. If not, unhide column E
If cell F1:=0, then Hide Column F. If not, unhide column F
If cell G1:=0, then Hide Column G. If not, unhide column G
If cell H1:=0, then Hide Column H. If not, unhide column H
If cell I1:=0, then Hide Column I. If not, unhide column I

See attached sample workbook for the set up. I must say that I am relatively new to using excel macros and programming in excel. Any detail you all can provide would be very helpful. Thanks in advance. Regards,

Taghos


Every time I find myself doing 100's of mouseclicks I reach out to you guys for help . . .taking a long shot here . . .
I have a worksheet with 40 columns of data; 20 or more of which the client doesn't need to see (they get confused easily). But we send this sheet back and forth. So I have to unhide all columns, do my thing, then hide them again . . . Here is what I was thinking . .
If I format the column headers in yellow, for example, for the columns the client needs, and Blue for the columns they don't need, is there a way to automatically hide columns for the columns with a Blue header cell?

I can do the macro to automatically unhide all cells, but want to be able to quickly re-hide them.

Thanks


I have 10 rows of data. Is it possible to have some macro that says 'if in row A, any of the cells contain "1", then show columns B&C. If all of those 10 cells in column A contain anything other than "1", then hide columns B&C'. Default should be that B&C are hidden.

This needs to be real-time. I.e. As soon as I enter "1" in those cells, columns B&C need to immediately un-hide.

I am terrible with macros so any specific code would be gratefully received.


I am trying to figure out a way to hide columns based upon how many columns are in use. For example, I have multiple motor types in A1:A15 and information about them in B:N.
Columns O and on contain information I gather upon inspection (Right now I have info in Columns O:Z).

So, what I want to do is create some kind of formula that states if columns O:forever have information hide all these rows EXCEPT the last four. For my example above (info in columns O:Z) I would want this to hide columns O:V. If I where to add another column (now O:AA) i would want it to know that it now needs to hide O:W automatically.

If this isn't clear please let me know and I will try to be more specific.

I'm not sure if this is possible, but would appreciate any suggestions.

Thanks,
Zanatos

OS - Windows XP
Excel 2003


I am somewhat a novice with VBA, in other words, I am still learning. I am trying to write a vba macro that would hide columns based on what is presented in another table. This will allow me to update the table and not have to worry about updating the macro on a continuing basis.

For example.

Columns
A1: Football
B1: Basketball
C1: Baseball
D1: Hockey - HIDE
E1: Soccer - HIDE

Table Hide:
Hockey
Soccer


OR

Would you suggest creating an if statement that would assign anything in the table to be equal to = X.
Therefore, writing the code to hide columns in A1:E1 = X.

Thank you in advance,
~ Jason


I love this place. The more questions I have answers, the more dependent I become because the more I realize is possible, that I can't do.

This time, I'm trying to hide columns based on values on certain cell values.

Here's the macro I'm using...
Code:

Sub HideEmptyColumns()
 Dim cell     As Range
  For Each cell In ActiveSheet.Range("AL155:EZ155")
    If cell.End(xlDown).Row = Rows.Count Then
    cell.EntireColumn.Hidden = True
    End If
 Next cell
End Sub


I WANT the macro to look at cells and if the cell is empty hide the column...
If AL155 is empty, hide Column AL
...
If EZ155 is empty, hide Column EZ

When I run this macro though, it just hides all columns from AL to EZ.

Do I have to create new macros for each column? or is it possible with a single macro?

Thanks again guys!


Hello;

I'm trying to protect/hide the formulas on a w/s by protecting the w/s so that I may not accidentally delete or overwrite them.
It works fine with the exception that the Hide & Unhide column no longer available either via Format::Column::Hide and Unhide or by a macro.

The macro to hide / unhide columns produces:
Run-time error '1004':
"Unable to set the Hidden property of the Range class"
and in the macro code:
.....Range("J1:K1").Select
...>Selection.EntireColumn.Hidden = True
is highlighted

Is there a workaround this difficulty; namely protecting the w/s formulas and still be able to Hide / Unhide columns ??

Thank you kindly.


Hello,

I have a sheet with 2 years of data
Columns B-M have Jan-Dec data for 2010 and N-Y have 2011.

When we are sending out the sheet we only want to show the current month and the previous years month.

So for Januarys version we would hide C-M and O-Y
In feb we would hide B and D-M and O and Q-Y and so on and so on...

I need a vba way of doing this as I have a preperation macro which applies all last min formatting and cleanup (removal of links etc) and this is the last issue I cant really resolved without going into the macro each month to fix it.

The reporting month we are in is in a cell in a sheet say A1 in sheet X, so the macro would need to read the date to understand what columns to hide.

Any ideas?

Thx


I have a Jet Report that I created which works great. After the report is created, it auto-hides any column or row that has a jet function in it to make the report easier to read. What i am trying to do is delete any column or row that has HIDE at the beginning. example below-

A B C D E F G
1 hide hide hide hide hide
2 hide
3
4 hide
5
6
7

After the report is saved, Columns ABCEF are hidden, but still there, i don't need this data any longer and would like to remove it from the document without affecting the original excel document.

any suggestions?


Sorry for posting this - I have been trawling the internet for the past day or so and just cannot seem to find anything that is suitable (I am an accountant and not completely conversant with macros).

I would like to hide columns when criteria in a column range is met...

Example, I would like to look at the range G73:DA81 and hide the column if the formulae is <=0. In other words, if range G73:G81 <=0, then hide column G, similarly H73:H81 <=0, then hide column H.....I, J, K etc.

Can you also provide me with the details to unhide them?

Many thanks


In my worksheet I have formulas in D35:CK35 that will result in "hide" or "show", what I would like is a Macro that when Run will hide the entire columns wherever the formula result is "hide". I'd also like to be able to click the button/run macro and then show all columns, so I can instantly switch from viewing all columns to only viewing those where the result is "show".

Many Thanks for any help,
Scoobyblue


Hi Guys

I have a sheet named QF - Final Fixtures

I have a cell on that sheet (AF15)

which will have either 3 words (Last 32, Last 16 or Quarter Finalists)

I have 3 columns

32 = AI - AS
16 = AU - BF
Quarter Finalists = BG - BP

What i need is

If cell AF15 says Last 32, then show/Unhide columns AI - AS but Hide columns AU - BF AND BG - BP
If cell AF15 says Last 16, then show/Unhide columns AU - BF but Hide columns AI - AS AND BG - BP
If cell AF15 says Quarter Finalists, then show/Unhide columns BG - BP but Hide columns AI - AS AND AU - BF
If AF15 = "", Then keep all the columns from AI - BP Hidden

I dont want to be pressing a button to hide/unhide. I want it to hide/unhide automatically based on the criteria as above.

Please help

Many Many thanks


Just to save one hour...

Perform this test :
Enter into the 256 available columns, row 1, anything you want, just to use all the columns;
Enter a commentary in the cell(1,254).

Now try to hide columns (1 to 4 ) : you will get a message :
"Impossible to move objects outside of the worksheet"
And your action will not be executed.

Stupid, isn't it ?

Just cancel the commentary and hiding columns will again be possible.

With a positive mindset, this is a way to forbid users to hide columns...


Hi All !

I want to hide Cells (In Row or Column) which are blank that they have no data to display.

I want to show only that cells (In row or Column) which certain data.

currently I am doing it Manually by Hiding Rows & Columns.

Is it possible that it should be done automatically by using macros or some functions.

Here is the detail of my query

A1 B1 C1 D1 E1
L-1 L-2
01/11/2006 PG To RA 0 PG To RA 2
RA To PG 0 RA To PG 1
PG To GF 0 PG To GF 0
GF To PG 0 GF To PG 0
RA To GF 0 RA To GF 0
GF To RA 0 GF To RA 0
Although column B has the data but all should be based on the Column C & E which the data in number form.
If these two columns C & E has the data "0" then these columns should be automatically hide & similarly for all other columns & rows.

If anyone can help me>>>>>>>>>


Hi All,

I am looking or a code, (Or formula if one exists) to hide or un-hide columns depending on what is entered into a cell.

I have attached an example to help clarify what i mean.

Basically i want:

If A2 is empty, hide columns C, D, E & F
If A2 = Apple, Unhide columns C & D, but keep E & F hidden
If A2 then becomes Banana, rehide C & D and unhide D&F

Any help on this will be greatly appreciated
Thanks in advance,
Dave


Hi,
I am stuck up in following condition using macro...

I have large data in worksheet having 200 Columns and 60 rows in "sheet1".

Concept is -
User inputs some number where that number's column only be visible and rest 199 columns and their data will be invisible.
( Eg - number entered 2. Column No 2 will be visible only; rest of columns 1 & 3-200 will be invisible)

Now Everytime as user enters number that column will be visible column.
So i have range of 0 to 200 numbers, any of which user can enter and other than selected column should be invisible.

i am not getting how to handle 200 columns data visible n invisible. Here its not any specific column to hide and show rest all.
Everytime there will be differnt number entered and different columns to hide.

How to handle such chunk of data with minimum code.

Anyone can help me please give suggession.

Thnx
HSD


I searched to see if my question has already been answered and was unable to find it, so if I overlooked an already provided answer please forgive me and point me in the right direction.

I would like to hide all columns where the cell value in row 5 of those columns is less than the value of cell U5.

NOTE: This report is very dynamic (ie. the number of columns is always different and therefore cell U5 will not always be the cell to compare to.

Thank You.


Hi to all.

Im looking for code which will show number columns depending about value in cell A1

In cell A1 user enter some number, min 1 max 60
what i need is that code show number on columns multiple with that number

so if number 3 is entered, code will show columns A B C D E F, if 5 A B C D E F G H I J and so on..
question is which one to hide, hide remain columns to column BI, including BI

what I need next is to delete values from hidden columns, not formatting, only values, cause i have some drop lists there

i hope this is easy for you experts and ull be quick

i know some simply code with plenty if then else statements, but im sure there is some easier way to do it


I have a spreadsheet that has 28 columns for time entries. Typically only the first 12 columns are used, so I would like to hide the remaining 16 columns (which makes the spreadsheet much more user-friendly). It would probably be nearly impossible to teach all of them how to Unhide the remaining columns (and re-Hide), plus I would like to use the full-screen function when employees enter thier times.

I would like to use a form control in the column heading so that when the employees 'check' it, it will Unhide and then re-Hide the columns.

Any way to do this? Seems like a VB thing to me (out of my league, but would be happy to add one in!).

Thank you,
Ray


I have a spreadsheet used to track orders and I want to create a way to print a receipt. Column A from row 2 to 528 is a list of items. Columns B and C are prices. I want to print these 3 columns plus the next two visible columns, but unfortunately which columns those will actually be varies. I already setup a macro to hide all unnecessary columns but depending on which column you select, the 4th and 5th visible columns could be anything from D to BT.

Here is the hard part though, without knowing exactly which column it will be (in terms of letter), I need to search the 4th column for blank cells and hide the corresponding rows. The 4th column is the number of each item in the order, but of the 528 items only 30 or so different items are likely to appear on a single order. So I want to be able to hide (or at least not print) all of the rows that correspond to an empty cell in that column and only print the rows that contain a number in that column.

I also need to include rows 1 (the header) and 529 (price total) in the final print.


Hi there,

I have different months worth of data on one sheet and would like to be able to choose which month to show so all other columns would be hidden.

For example, I may need to hide from A-F and also L-N. The visible area would only be G-K.

The code below hides from A-F however I can't seem to figure out how to get this to work in conjunction with the other columns I want to hide.

Code:

Sheets("Summary").Select
Columns("A:F").Hidden = True


Also, is it possible to mix hidding columns and rows? I'm hoping it might just be as simple as replacing columns with rows?

Thanks ever so much
Rich