 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2006Excel 2003 - Hiding Columns Based on a Condition
I have a simple Excel spreadsheet with two worksheets. I want to hide a block of columns on Sheet 2 if there is text (e.g. a date) in a cell on Sheet 1. For example if Sheet1!A2 has a date in it, then hide or show columns A through D on Sheet 2. The block of columns on Sheet 2 is centered around a date I would like to compare to the date in the cell on Sheet 1. I've made it so columns A though D are headed by the date in Sheet1!A2. I thought this might help if I needed data for a conditon. For example if columns A through D were for the dates 1/1/06 through 1/15/06, and there was no date in Sheet1!A2, columns A through D would be shown. Else, if Sheet1!A2 was greater than or equal to 1/15/06, columns A through D would be hidden and instead columns E through H on Sheet 2 would be shown. This would repeat down the line for the next set of dates and columsn, 1/16/06 through 1/31/06 and columns E through H and I through L. Is this lunacy or can this be done is reasonable fashion without breaking out the FORTRAN book? I'm no VB or VBA guy but this sounds like it needs a macro. Somebody lend this crazy Pollack a hand! Ben Tarnowski BenTarnowski@gmail.com I think I understand...(but who knows???): I use A2 of sheet1 to find a date. If the date is within any two week period, I show that period. A small table may help: Start Date End Date Show this column + 3 more 01/01/2006 01/14/2006 $A$1 01/15/2006 01/28/2006 $E$1 01/29/2006 02/11/2006 $I$1 02/12/2006 02/25/2006 $M$1 02/26/2006 03/11/2006 $Q$1 03/12/2006 03/25/2006 $U$1 03/26/2006 04/08/2006 $Y$1 04/09/2006 04/22/2006 $AC$1 04/23/2006 05/06/2006 $AG$1 05/07/2006 05/20/2006 $AK$1 05/21/2006 06/03/2006 $AO$1 06/04/2006 06/17/2006 $AS$1 06/18/2006 07/01/2006 $AW$1 07/02/2006 07/15/2006 $BA$1 07/16/2006 07/29/2006 $BE$1 07/30/2006 08/12/2006 $BI$1 08/13/2006 08/26/2006 $BM$1 08/27/2006 09/09/2006 $BQ$1 09/10/2006 09/23/2006 $BU$1 09/24/2006 10/07/2006 $BY$1 10/08/2006 10/21/2006 $CC$1 10/22/2006 11/04/2006 $CG$1 11/05/2006 11/18/2006 $CK$1 11/19/2006 12/02/2006 $CO$1 12/03/2006 12/16/2006 $CS$1 12/17/2006 12/30/2006 $CW$1 12/31/2006 01/13/2007 $DA$1 01/14/2007 01/27/2007 $DE$1 01/28/2007 02/10/2007 $DI$1 If that's close, then right click on the worksheet tab that has the columns that get hidden/shown. Select view code and paste this in: Option Explicit Private Sub Worksheet_Activate() Dim iCol As Long Dim myDate As Date Me.Columns.Hidden = True myDate = Me.Parent.Worksheets("sheet1").Range("A2").Value iCol = Int((myDate - DateSerial(2006, 1, 0) + 13) / 14) iCol = ((iCol - 1) * 4) + 1 If iCol < 1 Then iCol = 1 End If If iCol > Me.Columns.Count Then iCol = Me.Columns.Count - 3 End If Me.Cells(1, iCol).Resize(1, 4).EntireColumn.Hidden = False Me.Cells(1, iCol).Select End Sub (Darn arithmetic took way too long <vbg>!) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm > I have a simple Excel spreadsheet with two worksheets. I want to hide a > block of columns on Sheet 2 if there is text (e.g. a date) in a cell on [quoted text clipped - 22 lines] > Ben Tarnowski > BenTarnowski@gmail.com  SignatureDave Peterson I think you gave me a good starting point, now I need to translate it to my application. The sheet names are too long to type quickly so I'll call them Sheet1 and Sheet2. The cell on Sheet1!A1 is "=TODAY()" giving me my comparative point. Because if this date, columns K through O on Sheet2 should be shown be shown (sorry, it was five columns not four). The problem is this: columns K:O are given a date when I enter one. This date is not always, for example, the 2nd and 4th Friday of the month. I can be any date, but it always falls somewhere in the middle and at the end of the month. Again for example, columns K:O would be for the first half of this month and P:T for the second half. Say the next date I entered was the 1/15. Columns K:O would become hidden and P:T would become visible. Then when I entered the next date, say 1/30, P:T would become hidden and the next five columns would be visible. Are you saying that you have that table you provided somewhere in your worksheet, or was that for example and the code figures out the date range automatically? Thanks for your help. I used a two week interval and the code just did some arithmetic to deterime which columns should be seen. But if your dates vary, you may want to create a table with dates in one columns and the addresses of the columns that should be visible. Then you could look at that table, find the match and unhide those columns. > I think you gave me a good starting point, now I need to translate it > to my application. The sheet names are too long to type quickly so I'll [quoted text clipped - 20 lines] > > Thanks for your help. SignatureDave Peterson My logic is like this: Sheet1!A1 is today's date Sheet2!K1 is the merged column above the other 5 columns I want to hide. Sheet2!K1 already contains an IF condition as follows: =IF('Sheet1'!A5,'Sheet1'!A5,TODAY()) where A5 is the date I enter on Sheet1, or it can display TODAY() if there is no date entered. So I think something along these lines would work, but I don't know how to translate it into VB: IF(Sheet2!K1 <= Sheet1!A1) THEN Show Sheet2!$K$1:$O$1 Hide Sheet2!$P$1:$DZ$1 ELSEIF(Sheet2!K1 > Sheet1!A1) THEN Hide Sheet2!$K$1:$O$1 ENDIF And some of my variables are too specific. I need to look at more than K1; more like the whole set (K1, P1, U1,...,DV1). And there has to be some way of associating a set of columns with those cells (K1=K:O, P1=P:T, U1=U:Y,...,DV1=DV:DZ). Or maybe you're right and i need an extra table to keep track of the columns to be hidden. Can that be set in the worksheet code (e.g. ColSet1=$K$1:$O$1)? You could set up that list in your code, but personally, I find making changes on a worksheet much easier. I'd put all that info on a worksheet and maybe hide that worksheet so that it doesn't get harmed. > My logic is like this: > [quoted text clipped - 21 lines] > extra table to keep track of the columns to be hidden. Can that be set > in the worksheet code (e.g. ColSet1=$K$1:$O$1)? SignatureDave Peterson I made the table but I don't understand how you are directing hte script to hide a defined set of column. I'm not quite sure how you made the table, but let's say you have your dates in column A and your addresses in column B. I created a worksheet (named Index) and made it look like: 01/05/2006 A1:B1 01/06/2006 C1:E1 01/07/2006 F1:G1 01/08/2006 H1:L1 01/09/2006 M1:Q1 Then I could use: Option Explicit Sub testme() Dim IndexWks As Worksheet Dim wks1 As Worksheet Dim wks2 As Worksheet Dim testRng As Range Dim res As Variant Dim myDate As Date Set IndexWks = Worksheets("index") Set wks1 = Worksheets("sheet1") Set wks2 = Worksheets("sheet2") myDate = wks1.Range("a1").Value res = Application.Match(CLng(myDate), IndexWks.Range("a:a"), 0) If IsError(res) Then 'date not in table Beep Exit Sub End If Set testRng = Nothing On Error Resume Next Set testRng = wks2.Range(IndexWks.Range("b:b")(res).Value) On Error GoTo 0 If testRng Is Nothing Then MsgBox "design error--not a valid address" Exit Sub End If With wks2 .Columns.Hidden = True testRng.EntireColumn.Hidden = False .Select testRng.Cells(1).Select End With End Sub To show that associated range. > I made the table but I don't understand how you are directing hte > script to hide a defined set of column. SignatureDave Peterson Your code has been very helpful. I'm trying to write my own version with my own tweaks (this is my first time messing around with VB). I have the following so far (I've stopped at February): Public Sub HideColumn() Dim paydate1 As Date Dim paydate2 As Date Dim paydate3 As Date Dim paydate4 As Date Dim paydate5 As Date Dim paydate6 As Date Dim paydate7 As Date Dim paydate8 As Date Dim paydate9 As Date Dim paydate10 As Date Dim paydate11 As Date Dim paydate12 As Date Dim paydate13 As Date Dim paydate14 As Date Dim paydate15 As Date Dim paydate16 As Date Dim paydate17 As Date Dim paydate18 As Date Dim paydate19 As Date Dim paydate20 As Date Dim paydate21 As Date Dim paydate22 As Date Dim paydate23 As Date Dim paydate24 As Date Dim IndexWks As Worksheet Dim wks1 As Worksheet Dim wks2 As Worksheet Dim mydate As Date Dim Day As Date Dim Month As Date Dim ActMonth As Date Dim ActDay As Date Set IndexWks = Worksheets("Index") Set wks1 = Worksheets("Paychecks & Deductions - 2006") Set wks2 = Worksheets("CCs & Bank Accts. - 2006") wks2.Range("K2:DZ2").Select Selection.EntireColumn.Hidden = True mydate = wks1.Range("a1").Value ActMonth = IndexWks.Range("c1").Value ActDay = IndexWks.Range("c2").Value paydate1 = wks1.Range("a5").Value paydate2 = wks1.Range("a6").Value paydate3 = wks1.Range("a7").Value paydate4 = wks1.Range("a8").Value paydate5 = wks1.Range("a9").Value paydate6 = wks1.Range("a10").Value paydate7 = wks1.Range("a11").Value paydate8 = wks1.Range("a12").Value paydate9 = wks1.Range("a13").Value paydate10 = wks1.Range("a14").Value paydate11 = wks1.Range("a15").Value paydate12 = wks1.Range("a16").Value paydate13 = wks1.Range("a17").Value paydate14 = wks1.Range("a18").Value paydate15 = wks1.Range("a19").Value paydate16 = wks1.Range("a20").Value paydate17 = wks1.Range("a21").Value paydate18 = wks1.Range("a22").Value paydate19 = wks1.Range("a23").Value paydate20 = wks1.Range("a24").Value paydate21 = wks1.Range("a25").Value paydate22 = wks1.Range("a26").Value paydate23 = wks1.Range("a27").Value paydate24 = wks1.Range("a28").Value Day = Int((mydate - DateSerial(2006, ActMonth, 0))) Month = Int((mydate - DateSerial(2006, 0, ActDay))) If Day >= 1 And Day <= 15 And Month = 1 Then wks2.Range("K2:O2").Select Selection.EntireColumn.Hidden = False Else If Day >= 16 And Day <= 31 And Month = 1 Then wks2.Range("P2:T2").Select Selection.EntireColumn.Hidden = False End If End If If Day >= 1 And Day <= 15 And Month = 2 Then wks2.Range("U2:Y2").Select Selection.EntireColumn.Hidden = False Else If Day >= 16 And Day <= 31 And Month = 2 Then wks2.Range("Z2:AD2").Select Selection.EntireColumn.Hidden = False End If End If End Sub I can't figure out how to put my data ranges on the index sheet into my IF/THEN statements. After the THEN statements I have the code select a set of columns and unhide them after the overall code begins by hiding all the row from K:DZ. I made the index sheet and defined a bunch of variables to connect to those ranges (like the range in A1 on the index sheet is "K2:O2). I also setup two cells on the index page that return the current day and current month as integers so I can pass them into variables (ActDay and ActMonth) so the code can use them to subtract from the current day, for comparison in the IF/THEN statements. Is this redundant? Should I simply reference the cells on the index sheet for these integers? Thanks for your help. I hope I can return the favor someday. Ben And am I way off I'm confused (not unusual for me!). Can you explain what you want to do again? I'm kind of thinking that it might be just loop through the dates you have in a list and then show the columns associated with those dates? But I don't get it right now. > Your code has been very helpful. I'm trying to write my own version > with my own tweaks (this is my first time messing around with VB). [quoted text clipped - 118 lines] > Ben > And am I way off SignatureDave Peterson I guess this is getting really confusing to both of us, even though the concept is simple enough. I want to hide 23 of 24 payperiods, with only the current one being shown. I look trough my statements and it seems that the payment is deposited on the 14/15th and 30th/31st of the month. I will go with the 15th/30th combo and link the dates in Sheet1 with the dates in my index file. I can then do the IF/THEN comparison and assign a set of columns to be shown. If I did know the day I was getting paid, I could put that in Sheet1 and compare it to the month and day of the year. Can I do that with the code I posted? Is this part going to work or have I created it improperly; will it return the integers I'm looking for? I created this part to get the integers for the current date, and I could setup another section like this to get the integers for the paydate. Day = Int((mydate - DateSerial(2006, ActMonth, 0))) Month = Int((mydate - DateSerial(2006, 0, ActDay))) I would like to something more along the lines of increment some of those variables I created. And possibly increment the columns being shown so I don't have 12 (or 24) IF/THEN statements. Again, your help is much appreciated. Ben You're going to enter one date and the columns associated with that pay period will be shown--all others hidden. Since the pay periods are the 15 and the 30th, you could enter one of those days in a cell. Then you could use some arithmetic to determine the pay period: =1+ ((MONTH(A1)-1)*2) + (DAY(A1)>15) For instance: Jan 3, 2006 = 1+ ((1-1)*2) + 0 (Day(a1)>15) will be a 1 if the day is greater than 15. evaluates to 1 Jan 18, 2006 =1+ ((1-1)*2 + 1 =2 March 18, 2006 will be the 6th period. In code: Option Explicit Sub testme() Dim myDate As Date Dim myPayPeriod As Long myDate = Worksheets("sheet1").Range("a1").Value myPayPeriod = 1 + ((Month(myDate) - 1) * 2) If Day(myDate) > 15 Then myPayPeriod = myPayPeriod + 1 End If MsgBox myPayPeriod End Sub The next step is to find out which columns should be shown for that pay period. I'm gonna take a wild guess <bg> and guess that it's not columns 1-4 for the first period, 5-8 for the second, and so forth. I'm gonna guess that you have headers that you want to see (maybe always see column A, or A:B, or ...). But after we know that, we can use that myPayPeriod and a little arithmetic to show those columns. Are those columns to show as straight forward (always the same number (4???) for each pay period)? > I guess this is getting really confusing to both of us, even though the > concept is simple enough. I want to hide 23 of 24 payperiods, with only [quoted text clipped - 23 lines] > > Ben SignatureDave Peterson Well, I have been posting replys but they haven't been showing up on my end until now. I went and made some code but it get a run-time error (#1004) saying that "Application-defined or object-defined error". I included my code after the next paragraph. I think it would work, just not well or be very tidy for that matter. To answer your question, there are 5 columns to be displayed per payperiod starting with K:O, P:T, U:Y,...,DV:DZ. Columns K:O have a merged cell considered K1, but everything from row 2 and down is separated into 5 columns. So for this payperiod, I should be showing columns K:O, and on the 16th I should be showing columns P:T. Public Sub HideColumn() Dim IndexWks As Worksheet Dim wks1 As Worksheet Dim wks2 As Worksheet Dim Month As Integer Dim Day As Integer Set IndexWks = Worksheets("Index") Set wks1 = Worksheets("Paychecks & Deductions - 2006") Set wks2 = Worksheets("CCs & Bank Accts. - 2006") wks2.Range("K2:DZ2").Select Selection.EntireColumn.Hidden = True Month = IndexWks.Range("c1").Value Day = IndexWks.Range("c2").Value If Day >= 1 And Day <= 15 And Month = 1 Then wks2.Range("K2:O2").Select Selection.EntireColumn.Hidden = False Else If Day >= 16 And Day <= 31 And Month = 1 Then wks2.Range("P2:T2").Select Selection.EntireColumn.Hidden = False End If End If If Day >= 1 And Day <= 15 And Month = 2 Then wks2.Range("U2:Y2").Select Selection.EntireColumn.Hidden = False Else If Day >= 14 And Day <= 28 And Month = 2 Then wks2.Range("Z2:AD2").Select Selection.EntireColumn.Hidden = False End If End If If Day >= 1 And Day <= 15 And Month = 3 Then wks2.Range("AE2:AI2").Select Selection.EntireColumn.Hidden = False Else If Day >= 16 And Day <= 31 And Month = 3 Then wks2.Range("AJ2:AN2").Select Selection.EntireColumn.Hidden = False End If End If If Day >= 1 And Day <= 15 And Month = 4 Then wks2.Range("AO2:AS2").Select Selection.EntireColumn.Hidden = False Else If Day >= 16 And Day <= 30 And Month = 4 Then wks2.Range("AT2:AX2").Select Selection.EntireColumn.Hidden = False End If End If If Day >= 1 And Day <= 15 And Month = 5 Then wks2.Range("AY2:BC2").Select Selection.EntireColumn.Hidden = False Else If Day >= 16 And Day <= 31 And Month = 5 Then wks2.Range("BD2:BH2").Select Selection.EntireColumn.Hidden = False End If End If If Day >= 1 And Day <= 15 And Month = 6 Then wks2.Range("BI2:BM2").Select Selection.EntireColumn.Hidden = False Else If Day >= 16 And Day <= 30 And Month = 6 Then wks2.Range("BN2:BR2").Select Selection.EntireColumn.Hidden = False End If End If If Day >= 1 And Day <= 15 And Month = 7 Then wks2.Range("BS2:BW2").Select Selection.EntireColumn.Hidden = False Else If Day >= 16 And Day <= 31 And Month = 7 Then wks2.Range("BX2:CB2").Select Selection.EntireColumn.Hidden = False End If End If If Day >= 1 And Day <= 15 And Month = 8 Then wks2.Range("CC2:CG2").Select Selection.EntireColumn.Hidden = False Else If Day >= 16 And Day <= 31 And Month = 8 Then wks2.Range("CH2:CL2").Select Selection.EntireColumn.Hidden = False End If End If If Day >= 1 And Day <= 15 And Month = 9 Then wks2.Range("CM2:CQ2").Select Selection.EntireColumn.Hidden = False Else If Day >= 16 And Day <= 30 And Month = 9 Then wks2.Range("CR2:CV2").Select Selection.EntireColumn.Hidden = False End If End If If Day >= 1 And Day <= 15 And Month = 10 Then wks2.Range("CW2:DA2").Select Selection.EntireColumn.Hidden = False Else If Day >= 16 And Day <= 31 And Month = 10 Then wks2.Range("DB2:DF2").Select Selection.EntireColumn.Hidden = False End If End If If Day >= 1 And Day <= 15 And Month = 11 Then wks2.Range("DG2:DK2").Select Selection.EntireColumn.Hidden = False Else If Day >= 16 And Day <= 30 And Month = 11 Then wks2.Range("DL2:DP2").Select Selection.EntireColumn.Hidden = False End If End If If Day >= 1 And Day <= 15 And Month = 12 Then wks2.Range("DQ2:DU2").Select Selection.EntireColumn.Hidden = False Else If Day >= 16 And Day <= 31 And Month = 12 Then wks2.Range("DV2:DZ2").Select Selection.EntireColumn.Hidden = False End If End If End Sub My code actually works, but it doesn't execute automatically when the file is opened. And I still get that damn message at the opening of the program which asks me if I want to enable macros. Can I get rid of that and make my code (or your code) execute automatically? I think this is pretty darn close: Option Explicit Sub testme() Dim myDate As Date Dim myPayPeriod As Long Dim dispWks As Worksheet Dim NumColsToSee As Long NumColsToSee = 5 Set dispWks = Worksheets("sheet1") myDate = Worksheets("sheet1").Range("a1").Value myPayPeriod = 1 + ((Month(myDate) - 1) * 2) If Day(myDate) > 15 Then myPayPeriod = myPayPeriod + 1 End If 'K:O, P:T, U:Y,...,DV:DZ. With dispWks .Range("K1", .Cells(1, .Columns.Count)).EntireColumn.Hidden = True .Cells(1, 10 + (NumColsToSee * (myPayPeriod - 1) + 1)) _ .Resize(1, NumColsToSee).EntireColumn.Hidden = False End With End Sub > Well, I have been posting replys but they haven't been showing up on my > end until now. I went and made some code but it get a run-time error [quoted text clipped - 7 lines] > separated into 5 columns. So for this payperiod, I should be showing > columns K:O, and on the 16th I should be showing columns P:T. SignatureDave Peterson I get a run-time error '9': subscript out of range. And my columns to display are on Sheet2, mydate is on Sheet1, and the index information is on a sheet called "Index". I'm confused about if this is working. From some private emails, I thought you had it working. > I get a run-time error '9': subscript out of range. And my columns to > display are on Sheet2, mydate is on Sheet1, and the index information > is on a sheet called "Index". SignatureDave Peterson Well, I have been posting replys but they haven't been showing up on my end until now. I went and made some code but it get a run-time error (#1004) saying that "Application-defined or object-defined error". I included my code after the next paragraph. I think it would work, just not well or be very tidy for that matter. To answer your question, there are 5 columns to be displayed per payperiod starting with K:O, P:T, U:Y,...,DV:DZ. Columns K:O have a merged cell considered K1, but everything from row 2 and down is separated into 5 columns. So for this payperiod, I should be showing columns K:O, and on the 16th I should be showing columns P:T. I guess this is getting really confusing to both of us, even though the concept is simple enough. I want to hide 23 of 24 payperiods, with only the current one being shown. I looked through my statements and it seems that the payment is deposited on the 14/15th and 30th/31st of the month. I will go with the 15th/30th combo and link the dates in Sheet1 with the dates in my index file. I can then do the IF/THEN comparison and assign a set of columns to be shown. Is this part going to work or have I created it improperly; will it return the integers I'm looking for? I created this part to get the integers for the current date, and I could setup another section like this to get the integers for the paydate. Day = Int((mydate - DateSerial(2006, ActMonth, 0))) Month = Int((mydate - DateSerial(2006, 0, ActDay))) I would like to something more along the lines of increment some of those variables I created. And possibly increment the columns being shown so I don't have 12 (or 24) IF/THEN statements. Again, your help is much appreciated. Ok, I replied with a long blurb and it didn't seem to post. I was able to make a list of the dates I will get paid. Now, if I can compare those dates to the current date, I should be able to choose which columns I would like to display, should I not? If I retrieve the integer of the day and month and compare them to my integers (the 15th and 30th days of the month, and an integer of the month), I should be able to make one true statement and display a set of columns. And how to I make it stop asking me if I want to enable macros everytime I open the xls? Hi Ben, you seem to have a similar problem to me in posts not appearing until after some considerable delay (so I don't know when this will appear). In answer to your final question, you need to go to Tools | Macro | Security, and here you have a choice of three levels - yours is probably set currently to Medium. Hope this helps, Pete Hi Pete Do you have to go through Google? I use IE6 (though I had been experimenting with Mozilla Thunderbird), and I connect directly to msnews.com as a mail service. Most of my postings appear very quickly. Occasionally, some posts don't appear as part of a thread, one just sees "Re : Whatever ......." with no attachment to the original posting, but in general I don't seem to experience anything like the problems you are by going through Google. I do use Google if I am searching for anything, but for normal reading and answering, I just use IE6. SignatureRegards Roger Govier > Hi Ben, > [quoted text clipped - 8 lines] > > Pete Thanks Roger, I might try doing that. Pete Pete, Thanks, I did that and it worked. I have Norton Anti-Virus which scan documents when they are opened. That should be more than enough security. > Hi Ben, > [quoted text clipped - 8 lines] > > Pete Ben, I'm glad the post finally showed and that it was of use to you - it says it was posted at 7:04 am on Jan 08 (a Sunday ! ), but I can assure you that I don't normally post replies at that time. I think it was on Friday night when I submitted it. Pete |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.