Hide Columns With If / Then Statement
Jun 23, 2013
I'm attempting to put code in that will hide 2 individual columns when a particular cell in another column has no value in it. The cell, however, has a formula in it, and I'm not sure if that is my issue.
Essentially, if the formula in A32 returns a value, I need columns M and P to Hide. If it doesn't return a value, I need them to stay put. The value returning in A32 is a number, but it can be treated as text as it is an ID number and is not needed for any calculation.
Here's how I have it set up now:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A32") "" Then
Columns("M","P").EntireColumn.Hidden = True
Else
Columns("M","P").EntireColumn.Hidden = False
End If
End Sub
It's not working - nothing is happening.
View 5 Replies
ADVERTISEMENT
Aug 22, 2009
for statement to hide rows VB. In VB, how would the following look:
View 2 Replies
View Related
Nov 22, 2006
I am filtering the data displayed in a chart by hiding columns. I would also like to filter the X-Axis labels by hiding columns. If I do this manually I have no problems but when I run the following macro the chart gives a reference error for the X-axis labels.
Sub ShowA2()
Application. ScreenUpdating = False
num = Sheets.Count
Sheets("X-Axis").Activate
Range(Columns(1), Columns(256)).Select
Selection.EntireColumn.Hidden = False
For a = 1 To 5
Sheets(num - a).Activate
If ActiveSheet.Name = "A2 Data" Then
Columns("A:Q").Select
Range("A10").Activate
Selection.EntireColumn.Hidden = False
Sheets("X-Axis").Activate
Columns("A:E").Select......................
View 3 Replies
View Related
Feb 3, 2014
I have downloaded some of my bank statements in excel format but they are just static data - ie, they are just numbers in boxes and the BALANCE column does not react when I take out a transaction.
I have put in a formula for the BALANCE column so it does now take its value from the previous day plus or minus transactions, but now I want to do additional things.
- How would I, for example, categorise several transactions as "HOLIDAY" [URL] ....... and then temporarily make them disappear so that I can see the effect of that on my balance? I can see how to hide/unhide transactions but that doesn't actually seem to have any impact on the balance column.
- Second query: how do I make my current spreadsheet a template so that when I download the next bunch of bank statements I can just apply all the formulae in this one to it?
View 1 Replies
View Related
Jul 28, 2009
I have a formula that looks at 5 columns. If any one of these 5 columns meets the specified criteria, it will "Fail" the row. What I want is, when the row is failed, to display the column that was failed. Well I have that part under control using the following formula.
View 2 Replies
View Related
Oct 28, 2009
Rows:
Each row contains a new client (a new entry)
Columns:Clients first and last name.
Case number
Case type - Case type can be AAA, BBB, CCC, DDD, EEE, (and many more).
Case outcome - Case outcome can be Granted (G) or Denied (D)
A case can be of type AAA, and be Granted.
A case can be of type MMM, and be Denied.
A case can be of type ZZZ, and be Denied.
Etc...
I am trying to keep track of how many cases of each type have been granted and denied. If you look at the file i attached, you will see that the table is a list of clients, with different case types and different outcomes to their cases.
The problem i am having is that i need a way to have a two-condition statement as such:
if case type is "AAA" and case outcome is "G", then add 1 to a "AAA GRANTED" column on another sheet.,....................
View 3 Replies
View Related
Jun 30, 2014
I was wondering if there is a more beautiful way of this formula
Code:
{=MAX(IF(IF(B14=A1:A12, B1:B12)>IF(B14=A1:A12, C1:C12),IF(B14=A1:A12, B1:B12),IF(B14=A1:A12, C1:C12)))}
A
B
C
1
A
10
10
[Code] ...
Highest B is 80
View 7 Replies
View Related
Apr 14, 2014
I'm trying to determine if one date meets the criteria to be considered 'on time'. I have two columns: estimated completion date and completion date. I want to compare the completion date to the estimated completion date and if completion date is <= completion date but is NOT = 1/1/2099 then I met my date. I've tried this several different ways and problem is I cannot make it work.
When estimated completion date is 1/1/2099 'on time' should be 'no'
When estimated completion date is not 1/1/2099 but is greater than completion date 'on time' should be 'no'
When completion date is <= estimated completion date and estimated completion date is not equal to 1/1/2099 'on time' should be 'yes'
And I have a null date to deal with also but if I can satisfy the first 3 criteria I can manipulate the null values manually.
View 9 Replies
View Related
Oct 2, 2013
I need a If statement that can be used on 2 columns.
If there is a 1 in the first column I want to indicate if the 2nd column is 50% or greater with a number 1.
View 3 Replies
View Related
Jan 11, 2010
I have a very large spreadsheet that I work in and then print from. When I print, I need to hide about 20 columns which I do manually. I am curious if there is a way I can do this easier. If I could somehow filter columns, or set mutliple columns to a name for a dopr dowm box, or possibly a macro.
View 9 Replies
View Related
Feb 1, 2007
I am trying to use an IF formula to do the following for each row in a large spreadsheet: If the value in column W = 0, show columns A,B,C and E. I have one formula I've used that doesn't work, but as posting what I think is the correct answer could get my post deleted, I won't put it here.
View 3 Replies
View Related
Sep 12, 2007
I have found vba to hide rows that contain zero values, but not columns.
View 6 Replies
View Related
Mar 31, 2014
Column A: T/F
Column B: True/False
Here's what I want in a sentence: If one of the cells in grouped row is "T", column B = True, if not then column B = False.
(row groups are separated by a blank row)
Example)
Col A,Col B
TTRUE
FTRUE
[Code] ....
I came up with following formula:
=IF(MATCH(TRUE,INDEX(ISBLANK(A1:A17),0),0)>MATCH("T",A1:A17,0),TRUE,"")
It checks to see if blank cell is found before "T" but it doesn't look rows above.
inincubus.grouprows-1.xlsx
View 3 Replies
View Related
Jun 16, 2009
I've come across a spreadsheet where certain rows and columns (typically top rows or left columns) are hidden; however, there's no way to unhide them (the unhide function is grayed out) and it doesn't seem to be protected or no visible macros/vba on the file.
View 3 Replies
View Related
Dec 19, 2008
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!).
View 2 Replies
View Related
Jun 11, 2009
I have created this macro (below) in a standalone spreadsheet and the expected results are that Columns A,B,C,D,G,H will be displayed after I run the macro.
But when I use the same macro in my production worksheet (columns and ranges adjusted accordingly) this macro creates the following results: Column A is displayed and all the rest are hidden (B,C,D,E,F,G,H). I am stumped as to why this occurs. Can you advice me as to how to get this macro to work and display A,B,C,D,G,H ?
View 7 Replies
View Related
Nov 13, 2009
I'm using Excel 2003 and want to hide columns D:E & I:J. I have script as follows which hides I:J but I am unsure how to get it to include the other 2 columns.
View 4 Replies
View Related
Oct 23, 2002
I want to create two buttons.. one name HIDE and the other UNHIDE.
What I need hide button to do when i click it is hide the 4 columns to the right (not always going to be columns b:e)
For the unhide button unhide the 4 columns to the right (not always going to be columns b:e)
View 9 Replies
View Related
Mar 5, 2013
I once saw an excel sheet where I could hide or unhide a section by some + and - signes above the column-letters.... I have searched for this but I only get the ordinary hide/unhide solutions.
View 4 Replies
View Related
Jun 12, 2014
Using VBA or by other means, I want to disable user entry in cells G1 to X1 if the user places a value in A1, similarly if the user places a value in A2 then disable user entry in cells G2 to X2, and so on.
View 7 Replies
View Related
May 15, 2007
I am using drop-down lists in a cell to filter out some data in a spreadsheet. Works great, but I would like to hide certain columns depending on which item is selected in the drop-down list. I have written a macro to do this, but I cannot get the macro to run automatically every time there is a change in the drop-down list.
View 9 Replies
View Related
Apr 9, 2008
The other day I had posted a thread asking if anyone could help me ensure certain columns were hidden when a .xls was opened.
Here-> http://www.mrexcel.com/forum/showthread.php?t=312938
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
1ABCD2Shop items3dateQ1, hide meItemSupplier, hide me401/01/2006q1 2006JuicePepsi505/01/2008q1 2008crispswalkers
View 9 Replies
View Related
Sep 5, 2008
My goal is to be able to hide certain columns in the workbook based upon certain criteria. Moreover, I need to be able to hide columns in a certain tab even if I working in a different tab (i.e. if I am in Tab "A", I want to be able to hit "Ctrl + Alt + X" and be able to hide columns in Tab "B").
In order to accomplish this, I selected the Visual Basic icon. I then clicked on the "Insert" drop down menu and selected "Module". I have included a sample of the code that I wrote and inserted in to the module (see below). This general pattern of code repeats itself for 16 different macros - all in this same module.
Sub Macro01()
Worksheets("Input - Historical Financials").Columns("A:AD").Hidden = False
Worksheets("Input - Historical Financials").Columns("D:AD").Hidden = True
End Sub
Sub Macro02()
Worksheets("Input - Historical Financials").Columns("A:AD").Hidden = False
Worksheets("Input - Historical Financials").Columns("F:AD").Hidden = True
End Sub
View 9 Replies
View Related
Feb 27, 2009
I have a list of 24 columns (12 months each for forecast and actual data). I want to put VBA code in the worksheet that will hide a column based on a cell value in each column. On line 4 there is a formula that returns either "show" or "no". The columns are AS:BP. The code needs to run on a change in any of the cells AS4:BP4.
View 9 Replies
View Related
Mar 25, 2009
Currently using this code to search Column D and hide if the cells are blank. How would I go about also hiding Column C as well, If D is blank?
I am using the spreadsheet as a grading template. The assignment name is under column C and the grade under column D. If there is no grade then I would like to be able to hide the unused assignment column along with the empty grade column.
Sub HideColumnsInd()
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Dim rCell As Range
For Each rCell In Range("D3:D48")
If rCell = "" Then
rCell.EntireColumn.Hidden = True
Else
rCell.EntireColumn.Hidden = False
End If
Next rCell
Application.EnableEvents = True
End Sub
View 9 Replies
View Related
Jun 4, 2009
I'm trying to hide columns M:R on sheet "1up" if cell N4 on sheet "Fill In" is blank and unhide those columns if N4 contains an "X". Columns AK:AP should also hide or unhide on "1up" if AD4 is blank or not on "Fill In".
I've pieced together the below code by recording macros and piecing together code I've found in other threads on here. I placed the code in Sheet 1 (Fill In).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("N4").Valuse = "X" Then
Sheets("1up").Select
Columns("M:R").Select
Range("M7").Activate
Selection.EntireColumn.Hidden = True
End If
Sheets("Fill In").Select
Range("N4").Activate
If Range("N4").Valuse = "" Then
Sheets("1up").Select
Columns("M:R").Select
Range("M7").Activate
Selection.EntireColumn.Hidden = False
End If
Sheets("Fill In").Select
Range("N4").Activate
End Sub
View 9 Replies
View Related
Oct 29, 2009
I have a sheet that shows multiple part numbers as the headings for the columns. The rows show each individual job/install. If there is an "x" in the column, then the part is needed and must be pulled by the warehouse. If there are no x's at all in the column, I would like the column hidden so that I can print out a concise "pick list" for the warehouse that only shows those items needed.
View 9 Replies
View Related
Aug 14, 2008
I am trying to let Excel automatically hide unneeded columns. A brief description:
In this sheet one should enter a number of "projects" into cell DW7 (7,127). The maximum number of projects is 60. Each project consists of 2 columns (with already prepared formats and functions). The projects are numbered from 1 to 60 (the actual project number is in Cell G6 (6,7), I6 (6,9)...).
For this purpose I wrote the following
Sub worksheet_calculate() 'Hide columns that are not needed
Application. ScreenUpdating = False
Dim i As Integer
Dim r As Integer
i = 1
For r = 6 To 126
If Cells(6, r + i) > Cells(7, 127) Then
Columns(r).EntireColumn.Hidden = True
End If
If Cells(6, r + i) <= Cells(7, 127) Then
Columns(r).EntireColumn.Hidden = False
End If..................
View 7 Replies
View Related
Jun 29, 2007
I have a spreadsheet that is updated monthly. THe spreadsheet has a column for each month of the year, plus other columns. I would only like to display the current month and all past months - with the future months being hid from view. SO each time the user opened the file all headers with future dates will be hidden from view. I only would like to see the past months and other other no date column information. Is this possible to do in excel?
View 8 Replies
View Related
Jul 17, 2007
Hide values in Column layout on pivot table i only want to show th total value as its based on quarter figures not per month and this way it looks like Quarterly totals are broken down per month and has to have 200 a mont whenits in fact 200 for the complete quarter, how can i had the 200 for each of the month and just have the total per country? This is what my pivot table looks like
county Month Quarterly totals
UK June 200
july 200
August 200
Uk Total 200
View 3 Replies
View Related
