How can I get A macro to hide all column except the month I want to see but show week starting day not just the month?

[tonywatsonhelp]

Hi Everyone,

I want a macro that when I run, it hides all column between H & AXX except this months Columns however I need to see the full week not just from 1st,

so for example if I chose Feb 2017 (the date I choose would be "01/02/2017") then 1st feb is a Wednesday so I need it from the Monday so i'm want Monday the 30 January to Sunday 5th of march.

any ideas how I can do this, every column has a date in it in row1

 

[friel300]

do you have a sample workbook?
its a little hard to visualize with your description.

 

[CROY1985]

Assuming You have your date selected in A1 - "01/02/2017"
In A2 i have inserted a formula which will help to determine which columns to hide (it finds the last day of the month):
=IF(MONTH(A1)=12,DATE(YEAR(A1),MONTH(A1),31),DATE(YEAR(A1),MONTH(A1)+1,1)-1)

Then the following vba code:

Sub test()
For I = 8 To 1324
If Cells(1, I + (7 - Weekday(Cells(1, I), vbMonday))) >= Cells(1, 1) And Cells(1, I - Weekday(Cells(1, I), vbMonday)) <= Cells(2, 1) Then _
Columns(I).EntireColumn.Hidden = False _
Else Columns(I).EntireColumn.Hidden = True
Next I


End Sub

 

[CROY1985]

If you cant put the helper formula in A2, you will need to substitute "Cells(2,1)" for whichever cell you can use to hold that formula...its in the format Cells(Row Number, Column Number)

 

[Snakehips]

ADVERTISEMENT

CROY1985,
A couple of hopefully constructive comments on your solution.
Your code does hide/unhide the desired columns but doing it by looping will be relatively slow.
Also, he formula you have in A2 for the end of the month can be replaced by the single EOMONTH() function.

I would be doing......

Sub Hide_UnHide()
'assumes dates in H1:AXX1 are sequential


Set MyDate = Range("A1")  '*** Edit to suit cell holding date of month of interest


'Check date
If Not Day(MyDate) = 1 Then Exit Sub
If Not (MyDate >= WorksheetFunction.Min(Range("H1:AXX1")) And MyDate <= WorksheetFunction.Max(Range("H1:AXX1"))) Then Exit Sub


Application.ScreenUpdating = False
cells.EntireColumn.Hidden = False 'unhide all columns


Eom = WorksheetFunction.EoMonth(MyDate, 0)  'end of month


'determine range columns and allow for date limist in row 1


MyCol = MyDate - Range("H1").Value + 8
SCol = MyCol - Weekday(MyDate, 2)  ' back to day before previous Monday


FCol = MyCol + Day(Eom) + 7 - Weekday(Eom, 2)  'forward to day after following Sunday


SCol = WorksheetFunction.Max(8, SCol)
FCol = WorksheetFunction.Min(1324, FCol)


'Hide columns
If SCol > 7 Then Range(cells(1, 8), cells(1, SCol)).EntireColumn.Hidden = True
If FCol < 1325 Then Range(cells(1, FCol), cells(1, 1324)).EntireColumn.Hidden = True


End Sub

Hope that helps.

 

[tonywatsonhelp]

Hi Croy1985,
Thanks very much for trying to help, I can see how your suggestion would work but as there is a lot of data I think it might run a little slow for my use.

Snakehips, this is great works like a dream,

Thanks so much for your help again

Tony

 

[CROY1985]

Didn't know about the EOMONTH() function, every day is a school day!

I'm only starting out with VBA so will take a closer look at your solution thanks