Vba Macro To Hide Certain Columns
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.
Similar Excel Video Tutorials
Hide Columns by Grouping
- See how to hide columns using the Grouping feature. The Grouping feature will had a collapse and Expand plus above the column for easy hiding and unhi ...
Hide Columns Copy Problem
- See how to Hide Columns, Select Visible Cells Only, Copy and then Paste just the "Not Hidden Cells". Also see how to do this with formulas. ...
Absolute & Relative Macro Fix Data
- Learn How To Use A Macro With Relative and Absolute References to Deal With Data With Variable Row Size!
A MUST TRICK IF YOU USE RECORDED MACROS. ...
Relative & Absolute Macro Recorder
- Learn about:
1.Relative & Absolute button in the Developer Ribbon
2.The fact that the Relative & Absolute button governs selection m ...
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.
Hi there... i hope that some kind soul will be able to help me in this.
I have a spreadsheet with about 200 columns and i need a macro to hide most of them. The column headers that i do not want to hide in the data tab is listed in a separate worksheet in column A.
The macro will read from column A, look in the data sheet header and hide those columns that are not in the list.
I also need a separate button to un hide all of them.
Thanks in advance guys!
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.
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:
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?
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!
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,
I have an excel worksheet that has filters on Columns B:G. What I want to do is set macros in the workbook that will hide certain columns based on any filters that are set.
For example, if a user were to set a filter on Column B, Columns C:G would hide. If a user were to set a filter on Column C, Columns B and D:G would hide. This would go on through the end of the columns that have filters on them.
Then I would also want a macro to unhide all columns, in the event one wanted to see everything.
Thanks for any help you all can give!!
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.
I have 300 plus coulmns. I want to hide about 80 of the columns. I know the columns to hide in two different ways.
1. I could hide every third column.
2. I could hide every column that contains a row that has the text "atot".
I would like to use a macro to do this, Excel might call it something other than a macro. I use a produce called Macro Express that could do this. I would like to use whats avaible in Excel to do this. I'm using 2007.
I've searched around and played with this some. What I'd like is to create a Worksheet macro for when the user picks a Outline Row level (on my subtotaled report), unused columns would be hidden.
Row level 5 = no hidden columns
Row level 4 = hide column D
Row level 3 = hide columns C & D
Row level 2 = hide columns B:D
Row level 1 = hide columns B:D
I can't figure out, first, how to use the current row level as a variable and secondly, what event I would use to launch the macro.
I have little knowledge of VBA so tried to record a macro that I can then assign to a button in order to hide certain columns of a worksheet.
The worksheet contains data on all members of staff. I want to script three buttons to display data for Department 1, Department 2 and All. On click, I want it to hide the columns irrelevant to that department but it seems to not want to do individuals, but all of them within that range.
For example, hide columns B, C, F, and H.
I've recorded the macro but upon replaying it (or using the code generated for the button) it hides all columns up to H.
Any ideas ladies and gents? As always, very grateful for any ideas!
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.
OS - Windows XP
The other day I had posted a thread asking if anyone could help me ensure certain columns were hidden when a .xls was opened.
Now Id like to be able to hide the columns only if row B of the colum contains the text "hide me". Please not the cell may contain other text, such as "Q1, hide me"
Anyone know if its possible?
eg I want to hide Cols B and D
Q1, hide me
Supplier, hide me
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.
D1: Hockey - HIDE
E1: Soccer - HIDE
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,
I have a spread sheet where I only enter data in some of the columns and others may not be used. How do I hide columns that have no values without having to go in and manually hide them? I am trying to avoid printing spreadsheets 30 pages long.
Hi. Something goofy is going on, and I was wondering whether or not someone might have a helpful tip...
I used Macro Recorder to create a macro which would effectively, upon clicking a Forms button, hide 6 columns in my worksheet. I activated Macro Recorder, CTRL-clicked the six columns that I wanted to hide, went to "Format -> Columns -> Hide", and the six columns were hidden. I then stopped the recording. What Macro Recorder generated was this:
' HideGBWEntries Macro
' Hides GBW Entry Columns
' Keyboard Shortcut: Ctrl+g
Selection.EntireColumn.Hidden = True
For some reason, when I run this macro, it hides columns C through Y!!
I do have some conditional formatting and formulas applied to some cells in the in-between, but I cannot, for the life of me, figure out why it hides all those columns.
I know it must sound like I don't have a clue what I'm doing, but what I'm reporting here is, in fact, what is happening, and I'm baffled.
Any ideas? Thanks!!
I am trying to create a macro to hide columns so that the viewer has the ability to view columns by criteria such as currency. I recorded a marco to hide certain columns. When i ran the marco it deleted all the columns...
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...
Dim cell As Range
For Each cell In ActiveSheet.Range("AL155:EZ155")
If cell.End(xlDown).Row = Rows.Count Then
cell.EntireColumn.Hidden = True
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!
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:
...>Selection.EntireColumn.Hidden = True
Is there a workaround this difficulty; namely protecting the w/s formulas and still be able to Hide / Unhide columns ??
Thank you kindly.
In row 7 of Columns G thru R (12 columns) of a worksheet called "output", I have dates for January 31st thru Dec 31st (in a given year). Based on an input cell that contains one of these 12 dates, I want to hide all columns (in that year) that are on or before that date. So, I could start with column G and hide it as well as up to 11 more columns immediately to its right. Example, if the input date were 7/31, I would want to hide the first seven columns and keep the last five. I guess column G would always be hidden with this algorithm.
Can anyone help me with the syntax of such a macro? Thank you very much!
Also, somewhere on the web, I seem to recall a site with loads of examples of EXCEL macros for doing common things. Often I can figure out how to adapt one of these. Can anyone help me to rediscover it?
I have 30 columns that the user could hide if they wished. Different users will need to look at different columns so the option to hide and unhide columns is needed.
I have written this code below to hide and unhide a column when a check box is checked on a user form. Ideally I would like it to work from a command button.
Is there a better way I could write this code to hide certain columns. Lets say columns A,C,D,F,G,I,M,P
Or will I have to write this for each column and checkbox
Private Sub CheckBox1_Click()
If CheckBox1.Value = False Then
Sheet1.Columns("A").EntireColumn.Hidden = True
If CheckBox1.Value = True Then
Sheet1.Columns("A").EntireColumn.Hidden = False
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.
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
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.
I need to be able to hide columns (6 columns, they all have info i need to hide) if a value in another part of that same column = Current (the word current).
So N11 = Current, when I run the macro it should hide Col N-S. Also, if Z11 = Current, when I run the macro it should hide Col Z-AE etc etc for All columns where row 11 = Current. Each section that I need to hide has 6 columns associated with it.