Excel 2003 - Hiding Columns Based on a Condition

[BenTarnowski]

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
(e-mail address removed)

 

[Dave Peterson]

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

 

[BenTarnowski]

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.

 

[Dave Peterson]

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.

 

[BenTarnowski]

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=DVZ). 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)?

 

[Dave Peterson]

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.

 

[BenTarnowski]

I made the table but I don't understand how you are directing hte
script to hide a defined set of column.

 

[Dave Peterson]

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.

 

[BenTarnowski]

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("K2Z2").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 KZ. 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

 

[Dave Peterson]

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.

 

[BenTarnowski]

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

 

[BenTarnowski]

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.

 

[BenTarnowski]

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?

 

[Dave Peterson]

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)?

 

[BenTarnowski]

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,...,DVZ. 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.

 

[BenTarnowski]

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,...,DVZ. 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.

 

[BenTarnowski]

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("K2Z2").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("CW2A2").Select
Selection.EntireColumn.Hidden = False
Else

If Day >= 16 And Day <= 31 And Month = 10 Then
wks2.Range("DB2F2").Select
Selection.EntireColumn.Hidden = False
End If

End If


If Day >= 1 And Day <= 15 And Month = 11 Then
wks2.Range("DG2K2").Select
Selection.EntireColumn.Hidden = False
Else

If Day >= 16 And Day <= 30 And Month = 11 Then
wks2.Range("DL2P2").Select
Selection.EntireColumn.Hidden = False
End If

End If


If Day >= 1 And Day <= 15 And Month = 12 Then
wks2.Range("DQ2U2").Select
Selection.EntireColumn.Hidden = False
Else

If Day >= 16 And Day <= 31 And Month = 12 Then
wks2.Range("DV2Z2").Select
Selection.EntireColumn.Hidden = False
End If

End If


End Sub

 

[BenTarnowski]

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?

 

[Dave Peterson]

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,...,DVZ.
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

 

[BenTarnowski]

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".