Hiding Of Columns Based On Reference Cells
Apr 25, 2006
I'm trying to determine a range of columns I would like to hide by applying HLOOKUP on certain reference cells. The following was what I did:
Sub HideColumns()
' HideColumns Macro
colx = Chr([A25].Value + 64)
coly = Chr([A26].Value + 64)
Sheets("Sheet 2").Select
Columns("" & colx & ":" & coly & "").Hidden = True
'Selection.EntireColumn.Hidden = True
Sheets("Sheet 1").Select
Range("B1").Select
End Sub
I'm referencing cells A25 and A26 as the range of columns I would like to hide in Sheet 2. I equated the cells in Sheet 1 to columns in Sheet 2. Unfortunately, the result was columns being hidden from column 1 to column x instead. I would greatly appreciate if anyone can kindly correct my macro. Also, may I ask how I can distribute the columns evenly after hiding/unhiding?
View 4 Replies
ADVERTISEMENT
Jan 28, 2010
I have a spreadsheet set with the days in sach column (eg 29-Jan-09, 30-Jan-09, 31 Jan-09, 1 Feb-09 etc). What I would like to do is hide all columns prior to a date which is entered into a cell/macro text box.
View 9 Replies
View Related
Dec 10, 2012
Date
#
Lname
Fname
[Code].....
With that said, I want to put the row number of a user in (I have a few thousand on this spreadsheet, and all the = data be automatically pulled based on the row number I put in cell B1. So I'm hoping to write in cell B1 the following - "143" (without the quotes), and the remaining cells in column B automatically pull that data based on that, so it would look like the following
Row #
143
Name:
=C143&" , "&D143&" "&E143
[Code]....
How do I write the functions to keep the columns the same, but change the row number based on the number I input?
View 3 Replies
View Related
Sep 8, 2013
I have a spreadsheet that I'm trying to hide specific columns. In Row 6 I have the day of the week (Sun thru Sat) and Row 7 with the respective date starting in column F to column ZZ.
I would like to hide all columns with Saturday and Sunday in row 6. I tried using VBA but I'm new to coding and can't figure out the correct syntax.
View 4 Replies
View Related
Aug 7, 2011
I am trying to hide columns where the Value in the cells on row 9 is "", that cell being populated with a formula where the result is "". However I am getting the Compile Error Message 'Next without For'. Any clues?
Sub GraphC()
Dim a As Integer
Dim ColumnVar As Variant
ColumnVar = Array("B", "C", "D", "E", "F", "G", "H", "I", "J", "K")
[Code] .........
View 8 Replies
View Related
Dec 7, 2006
I need to run this funtion from the starting column (N) to the last column ( dynamic).
Sub HideUnits()
Dim rngData As Range, rngHide As Range
Application. ScreenUpdating = False
With Sheet1
.Rows(1).Insert
.Range("B1").Value = "Temp"
Set rngData = .Range("N1:N" & .Cells(Rows.Count, "N").End(xlUp).Row)
rngData. AutoFilter field:=1, Criteria1:="*Units"
Set rngHide = rngData.SpecialCells(xlCellTypeVisible)
rngData.AutoFilter
rngHide.EntireRow.Hidden = True
.Rows(1).Delete
End With
Application.ScreenUpdating = True
End Sub
As you might be able to tell I need to hide all the columns with a specific word in the heading. I'm pretty sure I'd need to loop this somehow, but I'm not good with VBA.
View 3 Replies
View Related
Mar 20, 2014
the support this board has given me as I learn VBA. I have three columns - Q, R, and S. I only need to see columns R and S if the cell values don't equal those in column Q or each other. So if I have cell values like the ones listed in the example below, then I don't need to see columns R and S.
Q Header
R Header
S Header
50
50
50
[Code]....
View 4 Replies
View Related
Feb 27, 2008
I'm trying to hide columns based on information in another column. If that column contains "A" or "B", hide columns "U" through "W". If it contains "A", "B", "C", or "D", hide columns "V" through "W". Etc, etc., so on and so forth...
I've snipped the code I found earlier and modified it to (what I thought) was correct for my application, but it doesn't seem to do anything. (btw, should I get an error if it doesn't run correctly?)
Here is the code as modified:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "P:P" Then
If LCase(Target.Value) = "A" Or LCase(Target.Value) = "B" Then
Columns("U:W").EntireColumn.Hidden = True
ElseIf LCase(Target.Value) = "A" Or LCase(Target.Value) = "B" Or LCase(Target.Value) = "C" Or LCase(Target.Value) = "D" Then
View 9 Replies
View Related
Dec 1, 2009
I have an elaborate spreadsheet that I have made for my company. I will give a little background how I have it set up:
E6 contains a drop down menu of all products that we sell, and line items are populated based on the selection here. In columns N and O, I have a "Business Partner Costs" table that calculates a specific discount % based on the business partner.
Some of our products are sold to BPs with no discount. For these specific products, I am trying to get the BP Costs table to disappear, or hide.
Basically, I want something like: "=if(or(e6=ae2,e6=ae7,e6=ae12,e6=ae13,etc),hide columns N&O,show columns N&O)" in macro format.
View 9 Replies
View Related
Feb 15, 2010
I would like to write a macro that automatically hides columns of data
based on the value of a cell (I2) with a picklist. Cell I2's picklist is
monthly values (formatted as Jan-10 though Dec-10 but real values are
1/1/2010 through 12/1/2010). I have a range that contains work week end date
values (1/8/2010 to 12/31/2010) in L6:BK6. I would like to have the macro
hide columns that are less than date value chosen in I2.....
View 3 Replies
View Related
Oct 20, 2006
I have a worksheet that has >10 rows of data and over 150 columns. The values in the cells for individual row include NA, NE, D, A and S. Each row will have one or all of these values in one of the cells. Here is the question:
How do I hide columns containing 'NA' in the cell for a particular row, when only that row is selected by clicking on any cell in row 'A'? For example: If my row 3 cell values for column A, J, R, and X are 'NA' I want to hide the column containing 'NA' only and display everything else? And only when I click on row 3 column 'A'
View 2 Replies
View Related
Nov 4, 2008
I have an activeX combo-box that selects from different pieces of equipment that we supply. Based on that selection, I require ranges from the same page that the combo-box is on to either hide or unhide. Also, I require different tabs to become visible or hidden based on that same selection. So far so good - I have code that does this, and it appears to work without glitch.
Where the problem arises, is in one of the ranges that is unhidden when a particular piece of equipment is selected there is another combo-box that I would like to use (the number of said pieces of equipment to supply) to further hide/unhide additional ranges on the same page, and also hide/unhide certain tabs as well.
When I make a selection from combo-box 1, all works as planned, but when I change the state of combo-box 2, even with no associated coding referring to it, I cannot change combo-box 1 again without getting Error 1004 "Unable to get the Hidden property of the range class".
None of the sheets in the workbook are protected.
I would sincerely appreciate any help/code that could circumvent this error.
View 9 Replies
View Related
Dec 17, 2009
I have this model I created where I have two tabs. One tab is an input tab using validation and drop down menus and the other is a display tab. I simply want to hide certain QTRs based on the value of one of the drop down menu results. I tried writing the VBA code below but am a novice when it comes to code. Can somebody please help me fix the below code so that it works properly.
Public Sub hide()
If Worksheets("input").Range("b14") = "Q1" Then
Worksheets("Group P&L").Columns(c, d, e, h, i, j, m, n, o, r, s, t, w, x, y).Hidden = True
ElseIf Worksheets("input").Range("b14") = "Q2" Then
Worksheets("Group P&L").Columns(c, d, h, i, m, n, r, s, w, x).Hidden = True
ElseIf Worksheets("input").Range("b14") = "Q3" Then
Worksheets("Group P&L").Columns(c, h, m, r, w).Hidden = True
ElseIf Worksheets("input").Range("b14") = "Q4" Then
Worksheets("Group P&L").Columns.Hidden = False
End If
End Sub
View 9 Replies
View Related
Feb 2, 2007
I'm trying to hide all columns which have the word "hide" in row 6. I have done a similar thing whereby I hide all rows which have the word "hide" in column 3 using the following
Sub HURows()
BeginRow = 9
EndRow = 40
ChkCol = 3
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "hide" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub
Alas changing the number and every Col for Row and vice versa doesn't work!! Really I only need to search colums G to U inclusive. The code must also unhide colums if the values in the cells of row 6 change to anything other than "hide".
View 2 Replies
View Related
Dec 5, 2008
I have a project that needs to be done today. What I'm trying to do is hide an area of a worksheet using a button click, then display that area when the button is clicked again. This area is approximately the lower right quadrant of the worksheet, so it shares column and row information with other data that must remain visible. Is there any way to do this?
Alternatively, I was thinking about making a duplicate of the worksheet, but omitting the area I want to hide on the duplicate sheet. The button would then hide and unhide the two worksheets, hopefully making it have the same effect as hiding or unhiding just that area. If I do this, I would need to maintain the exact same data in the visible area of the two sheets.
View 13 Replies
View Related
Sep 4, 2013
I created a macro that analyzes some datasets and according to different parameters it puts an X at the side of every cell that goes out of parameters. These Xs are all in columns named the same way for it to be easier to find. What I want to do is to write some macro that will hide every row where the data did not go out of parameters to be able to easily see where it did. In the following example it would need to hide the second row since there are no Xs in that row. One of the problems I'm having is that the amount of columns and rows is variable.
Data
Data
Functional Upset
Data
Data
Functional Upset
#
#
X
#
#
[Code] .......
Below is the piece of code that I created to try to do this (G has the value for the amount of rows). I think it is working but it is either taking too long since it has to sometimes go through over 20k lines as much times as it needs to to cover all the rows that contain Xs or its getting stuck for some reason.
Code:
Worksheets(2).Range("I3:I" & G).EntireRow.Hidden = True
K = 0
Do Until Worksheets(2).Range("I1").Offset(0, K) = ""
If Worksheets(2).Range("I1").Offset(0, K) = "Functional Upset" Then
[Code] ......
View 6 Replies
View Related
Apr 18, 2014
I'm using the below macros to hide or unhide rows based off of a value in column A. Column A contains an If statement that looks for a value in column F, and depending on whether the corresponding cell in column F contains a value the if statement returns either True or NO-IF(F>0,True,"No).
It currently takes quite a bit of time to run this macro. Given the specs on this machine, I'm convinced that there is probably some loop or inefficiency in my code.
Sub Button1_Click()
Dim cell As Range
For Each cell In Range("a59:A1472")
[Code]....
View 3 Replies
View Related
Sep 26, 2006
I'm using Conditional Formatting for an entire column to check the value of each cell and compare it to the value of a cell in the same row but a different column. (Cell Value equals =$D2) It works great until I insert or delete a cell in the formatted column. The reference does not change as one would expect. I've played around with formulae such as =$D2<>$P2 but the reference only changes for the P2, not the D2. I've also tried using =CELL("contents", ADDRESS(Row(),4)) but this causes excel to complain.
View 3 Replies
View Related
Jul 25, 2013
Sheet1 is a report that expresses 90 days of information and Sheet2 has three columns of 365 day information. Sheet1 has a title, a today function for the date (cell B2), two columns (A5:A94 and B5:B94), and then some other info not relevant to the problem. Sheet2 has three columns (A1:A365, B1:B365, and C1:C365) with the date in column A starting at the first of the year and then two lists of information in columns B and C. I am wondering if there is any way excel would be able to recognize the Date (B2) on Sheet1 and be able to pull the information from Sheet2 starting on that date for the next 90 days.
For example:
My report always starts on the day I am accessing it. So... Say it is 1/15/13. My today function in Sheet1 (B2) would express 1/15/13. This date would be equal to A15 on Sheet2. So I would want A5 & B5 on Sheet1 to return B15 & C15 from Sheet2 respectively.
A5 Sheet1 = B15 Sheet2 & B5 Sheet1 = C15 Sheet2
A6 Sheet1 = B16 Sheet2 & B6 Sheet1 = C16 Sheet2
...
A93 Sheet1 = B103 Sheet2 & B93 Sheet1 = C103 Sheet2
A94 Sheet1 = B104 Sheet2 & B94 Sheet1 = C104 Sheet2
With that being said, the next time I open up my report, say a week from the 15th or 1/22/13 (expressed by my today function in B2), I would want A5:A94 & B5:B94 to reference the new set of information:
A5 Sheet1 = B22 Sheet2 & B5 Sheet1 = C22 Sheet2
A6 Sheet1 = B23 Sheet2 & B6 Sheet1 = C23 Sheet2
...
A93 Sheet1 = B110 Sheet2 & B93 Sheet1 = C110 Sheet2
A94 Sheet1 = B111 Sheet2 & B94 Sheet1 = C111 Sheet2
Is this at all possible?
View 8 Replies
View Related
May 27, 2014
I am trying to find a way to automatically highlight all the cells specified by the cell references in a column on another worksheet (which will constantly have new values added).
On the attached sample, the 'Data Before' tab shows the base data before any highlighting. As cell references are added to the 'References' tab in 'column A' I want the respective cell to be highlighted on the 'Data Before' tab resulting in the 'Data After' tab and each time a new cell reference is added to the 'References' tab, the respective cell is then highlighted on the 'Data Before' tab.
I would like to have control over the highlight colour so that a different highlight colour can be specified for each date change, i.e. all cell references that are shown on the same date are the same colour but where the date changes a different highlight colour can be specified.
Highlight by Cell Reference.xlsx
View 9 Replies
View Related
Mar 6, 2014
i need a macro which puts the formulas into the cells as per attached.
the number of rows between the "beam" can be variable
the number next to the beam will be variable.
the number of "beam" rows is vaiable
my thoughts where to do a find"beam" and refernce the cells address the create formulas off those points but dont have the skill to code this
beam macro.xlsx
View 2 Replies
View Related
Feb 9, 2013
How do you multiply two column vectors D2:D6 and G2:G6 based on reference cells A3 and B3 that are equal to D1 and G1?
A
B
C
D
E
F
G
1
2
3
4
5
[Code] ......
View 1 Replies
View Related
Nov 7, 2012
Basically, I need to copy and paste upon certain condition (references) and paste related-datas in another sheet corresponding with these references. Every references are organized by lines with indicators on columns.
In attached file, see:
- worksheet ANALYSIS:
* datas in E2, G2, D18, D19, D20, D21, D22 that needs to be copy based on reference in C2,
* then need to find this reference in worksheet called GROUP SAVE,
* and based on this reference, paste above cells value in corresponding "Indicators" column number
THEN, I will have to do pretty much the same with subgroup data. But I can figure out for this second step that request same manipulation.
View 3 Replies
View Related
Jan 30, 2010
I have been given the following code and it works great. I now need to adapt it to the following scenario: In the attached sheet, the user has to select either, "Suburban" or "Squad" in row 5. If the user selects, "Suburban" I need this script to compare the values they enter in a given row to the value in column "B".
If the user selects, "Squad" I need this script to compare the values they enter in a given row to the value in column "C".
Look at row 48, for example. If the user enters, "Suburban" in cell D5 then the value they enter in cell D48 should equal "1". If it does not equal "1" then it should proceed with the adding of a comment. Conversely, if the user enters, "Squad" in cell D5 then the value the enter in cell D48 should equal "2". If it does not equal "2" then it should proceed with the adding of a comment.
View 5 Replies
View Related
Jul 7, 2008
Here's what I'm trying to do -
There's a sheet called "Main" and 38 other sheets that shows the data (these 38 sheets all have the exact same structure/layout). Let's say these 38 sheets are called "country1", "country2", .... "country38".
Based on user input in cell J10 in "Main", I want to show only the relevant columns in all of the 38 country sheets. Specifically,
If J10 in sheet "Main" = "Option 1", hide all columns in all 38 sheets except columns A to W.
If J10 in sheet "Main" = "Option 2", hide all columns in all 38 sheets except columns Y to AU.
If J10 in sheet "Main" = "Option 3", hide all columns in all 38 sheets except columns AW to BS.
If J10 in sheet "Main" = "Option 4", hide all columns in all 38 sheets except columns BV to CQ.
If J10 sheet "Main = "ShowAll", show all columns in all 38 sheets....
Cell J10 in "Main" will be a drop down bar with the 5 choices.....
View 14 Replies
View Related
Mar 30, 2009
I have a spreadsheet with dates runnung from column D:IV,
I am running a simple macro but need this macro to hide columns D to whichever column is yesterday's date.
View 5 Replies
View Related
Oct 1, 2013
I have a large table with an unknown number of columns and an unknown number of rows. The table contains no formulas and no errors - only text, numeric values and empty cells. The top row contains text (headers).
Column I contains below the header only zeros and/or 1s. However, a few entries in Column I might also be empty cells. Same is true for columns I+4 (i.e., M), I+8 (i.e., Q), I+12 (i.e., U), etc.
How could I hide ALL such columns (i.e., I, M, Q, U, etc.) with a VBA macro?
View 7 Replies
View Related
Nov 23, 2006
I was wondering if there was a simple function that will hide all the columns which have Zero value thoughout a pricing spreadsheet.
The different elements of the pricing have lead to 60+ columns, upto 75% may not contain a value or may display "false", it is making it very difficult to view and print. At present I am manually auto filterig to see if values are present in the column and then hiding the columns if they are blank (a real pain in the backside).
View 9 Replies
View Related
May 20, 2007
I have a spreadsheet that i would like to hide all columns from B:M and to select the column/s I want to unhide.
I would like VBA cose to do this or to set up a combo box that will allow me to hide/unhide specific columns....
View 9 Replies
View Related
Dec 13, 2003
I need help on hiding a row if a cell value is 0 in a column
View 5 Replies
View Related
