Hide columns am /pm

have this file

need button to hide the am pm as needed

imean hide am then pm only show

then when pm hide only am show

button should be in column A8
29076815a--2-aaaaaaaaa.xlsm
ADRIANA PACCOUNTING ASSISTANTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
If you hide, say the AM columns the remaining columns will need to be made a little wider so that the date can be seen. In this picture I've widened Monday PM. Is that OK?Microsoft_Excel.jpg
1
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

In this picture I've widened Monday PM. Is that OK?

That will be  Good !
0
Martin LissOlder than dirtCommented:
Can there be more data past column DV?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Can there be more data past column DV?

Martin YES

as week comes  each week will be added so no limit column will be
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
all the columns will have data eventually
0
Martin LissOlder than dirtCommented:
Ok but I hope you realize that since each week on the sheet has 24 columns and there are 52 weeks then there will be 24 x 52 = 1248 columns + some others!
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
how many years can  be in the sheet ??
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
you  mean it can handle ??
 the 1248 coulmns ??
0
Martin LissOlder than dirtCommented:
Yes it can handle 1248 columns. The column limit is currently 16,384, so about 12 years, but you surely don't want to deal with that many columns. I wouldn't want to handle "just" 1248.

I think the best idea would be to have 1 sheet for every month, but even then we'd need to develop some way of navigating to a particular month's sheet, because after on'y 5 years you'd have 60 sheets.

It's past my bedtime so we'll talk more tomorrow.
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
ok Martin Have Great Sleep
 we keep going
0
Martin LissOlder than dirtCommented:
Good morning.

I've thought some more about this and I realized that having one sheet per month probably won't work, because except for February sometimes, no month has exactly 4 weeks.

If I understand your process better then maybe I could think of a better idea so for now let me ask you a couple of questions;
  1. How many weeks of past data do you need to be able to see? In other words four years from now will you still need to see data from 33-Jul-17?
  2. Do you need the rows 47 to 202?
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Good morning.
How many weeks of past data do you need to be able to see?

As data is put on  i keep like an history

In other words four years from now will you still need to see data from 33-Jul-17?


rigth now it begins in 33 JUl 17
then jump to
31W2017                        32W2017                                                                                                                                                      51W2017                        52W2017                                                                                                                                                      1W2018                              and keep going
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Do you need the rows 47 to 202?

yes data is all over
0
Martin LissOlder than dirtCommented:
Let me ask my first question in another way. Do you always need to be able to see all the data at the same time? If the answer is 'no' then it is possible to store the data in a database and request to see just the data you want to see,

Déjame hacer mi primera pregunta de otra manera. ¿Siempre necesita poder ver todos los datos al mismo tiempo? Si la respuesta es 'no', entonces es posible almacenar los datos en una base de datos y solicitar ver solo los datos que desea ver,
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Let me ask my first question in another way. Do you always need to be able to see all the data at the same time? If the answer is 'no' then it is possible to store the data in a database and request to see just the data you want to see,

ok thats  an great idea!

most of the time i need see the last 4 weeks sequence

but the others can be request as needed
0
Martin LissOlder than dirtCommented:
OK, to have this workbook show just 4 weeks (or however many weeks you want to show), you'll need to ask a new question. Make sure to include the Access topic. I'm not an Access expert so someone else will need to help you, but let me know the URL so I that if needed I can help explain what you need.

In the attached workbook you'll find a new button that says "AM". When you click it, just the AM columns will show up. When it says "PM", clicking it will show just the "PM" columns, clicking it again will show all columns.
29080693.xlsm
1
Martin LissOlder than dirtCommented:
Wait. I just noticed a problem. I'll be back soon.
1
Martin LissOlder than dirtCommented:
Merged cells are a pain to work with!
0
Zeth LarssonCommented:
Adriana P
First you schould remove all merged cells,
You can get the same effekt by selecting the cells over which you want to center the text, and then select format cells, and in the drop down horisontal select: center over selection. Do that for the date, day and AM/PM text.
Then it will be possible to select any number of columns, as it is not possible by VBA to select individual column within merged cells.
Then move date, day and AM / PM text to the first column of each AM and PM. It schould look something like this.
text to the left in AM and PM

Then paste the code below into a module, and the constants in another module
Some settings is nessessary,
It will also be a bit easier if you change reference to R1C1, then you get numbers instead of letters as column reference.
The loops are limited to 57 columns just for test, in this code.
Run test and hopefully built further!
' -------------------------------------------------------------------
Option Explicit

Sub PM_visible()
    hideAMPM "AM"
End Sub
Sub AM_visible()
    hideAMPM "PM"
End Sub

Sub ViewAMPM()
    viewALLAMPM "dummy"
End Sub

Sub viewALLAMPM(dummy As String)
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim intAMPMColumnCount As Integer
   
    Set wb = ThisWorkbook
    Set sh = wb.ActiveSheet
   
    ' Row intAMPMRow always value AM or PM every second column.
    For intAMPMColumnCount = intFirstAMPMColumn To intLastAMPMcolumn
        If sh.Cells(intAMPMRow, intAMPMColumnCount).Value <> "" Then
            Range(Cells(intAMPMRow, intAMPMColumnCount), Cells(intAMPMRow, intAMPMColumnCount + 1)).EntireColumn.Select
            Selection.ColumnWidth = 3.57
           
            ' Cehck if column is AM AND if Date is "" in the same column. Which means that PM was last visible columns
            If sh.Cells(intAMPMRow, intAMPMColumnCount).Value = "AM" And sh.Cells(intDateRow, intAMPMColumnCount).Value = "" Then
                'Move date and day as if AM is visible, and center over four columns.
                ' Move date and day
                Range(Cells(intDateRow, intAMPMColumnCount), Cells(intDateRow, intAMPMColumnCount)).Value = Range(Cells(intDateRow, intAMPMColumnCount + 2), Cells(intDateRow, intAMPMColumnCount + 2)).Value
                Range(Cells(intDateRow, intAMPMColumnCount + 2), Cells(intDateRow, intAMPMColumnCount + 2)).Value = ""
                Range(Cells(intDayRow, intAMPMColumnCount), Cells(intDayRow, intAMPMColumnCount)).Value = Range(Cells(intDayRow, intAMPMColumnCount + 2), Cells(intDayRow, intAMPMColumnCount + 2)).Value
                Range(Cells(intDayRow, intAMPMColumnCount + 2), Cells(intDayRow, intAMPMColumnCount + 2)).Value = ""

                ' Center date over selection
                Range(Cells(intDateRow, intAMPMColumnCount), Cells(intDateRow, intAMPMColumnCount + 4)).Select
                With Selection
                    .HorizontalAlignment = xlCenterAcrossSelection
                    .VerticalAlignment = xlBottom
                    .WrapText = False
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = False
                End With
                ' Center day over selection
                Range(Cells(intDayRow, intAMPMColumnCount), Cells(intDayRow, intAMPMColumnCount + 4)).Select
                With Selection
                    .HorizontalAlignment = xlCenterAcrossSelection
                    .VerticalAlignment = xlBottom
                    .WrapText = False
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = False
                End With
            End If
        End If
    Next intAMPMColumnCount
    Cells(1, 1).Select
End Sub


Sub hideAMPM(AMPM As String)

    If AMPM = "AM" Then
        If ThisWorkbook.ActiveSheet.Range(Cells(intDayRow, intFirstAMPMColumn), Cells(intDayRow, intFirstAMPMColumn)).Value <> "" Then
            doHideAM
        Else
            MsgBox "PM alredy visible. Run PM visible can damage the worksheet!", vbOKOnly, "AM VISIBLE"
            Exit Sub
        End If
    ElseIf AMPM = "PM" Then
        If ThisWorkbook.ActiveSheet.Range(Cells(intDayRow, intFirstAMPMColumn + 2), Cells(intDayRow, intFirstAMPMColumn + 2)).Value <> "" Then
            doHidePM
        Else
            MsgBox "AM alredy visible. Run AM visible can damage the worksheet!", vbOKOnly, "AM VISIBLE"
            Exit Sub
        End If
    End If
End Sub

Sub doHidePM()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim intAMPMColumnCount As Integer
    Dim strAMPMValue As String
    Dim dblColumnWidthVisible As Double
    Dim dblColumnWidthAllVisible As Double
    Dim AMPM As String
   
    Set wb = ThisWorkbook
    Set sh = wb.ActiveSheet
   
    intAMPMColumnCount = intFirstAMPMColumn
       
    For intAMPMColumnCount = intFirstAMPMColumn To intLastAMPMcolumn
        sh.Cells(intAMPMRow, intAMPMColumnCount).Select
        If sh.Cells(intAMPMRow, intAMPMColumnCount).Value = "PM" Then
            ' Make AM visible
            Range(Cells(intAMPMRow, intAMPMColumnCount - 1), Cells(intAMPMRow, intAMPMColumnCount - 2)).Select
            Selection.EntireColumn.Hidden = False
            ' Move date
            Range(Cells(intDateRow, intAMPMColumnCount - 2), Cells(intDateRow, intAMPMColumnCount - 2)).Value = Range(Cells(intDateRow, intAMPMColumnCount), Cells(intDateRow, intAMPMColumnCount)).Value
            Range(Cells(intDateRow, intAMPMColumnCount), Cells(intDateRow, intAMPMColumnCount)).Value = ""
            ' Center date over selection
            Range(Cells(intDateRow, intAMPMColumnCount - 1), Cells(intDateRow, intAMPMColumnCount - 2)).Select
            With Selection
                .HorizontalAlignment = xlCenterAcrossSelection
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
       
            ' Move day
            Range(Cells(intDayRow, intAMPMColumnCount - 2), Cells(intDayRow, intAMPMColumnCount - 2)).Value = Range(Cells(intDayRow, intAMPMColumnCount), Cells(intDayRow, intAMPMColumnCount)).Value
            Range(Cells(intDayRow, intAMPMColumnCount), Cells(intDayRow, intAMPMColumnCount)).Value = ""
           
            ' Adjust column width for visible columns
            Range(Cells(intAMPMRow, intAMPMColumnCount - 2), Cells(intAMPMRow, intAMPMColumnCount - 1)).Select
            Selection.ColumnWidth = 4.43
   
            ' Hide columns
            Range(Cells(intAMPMRow, intAMPMColumnCount), Cells(intAMPMRow, intAMPMColumnCount + 1)).Select
            Selection.EntireColumn.Hidden = True
           
        End If
    Next intAMPMColumnCount
   
End Sub



Sub doHideAM()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim intAMPMColumnCount As Integer
    Dim strAMPMValue As String
    Dim dblColumnWidthVisible As Double
    Dim dblColumnWidthAllVisible As Double
    Dim AMPM As String
   
    Set wb = ThisWorkbook
    Set sh = wb.ActiveSheet
       
'    intAMPMColumnCount = intFirstAMPMColumn
       
    For intAMPMColumnCount = intFirstAMPMColumn To intLastAMPMcolumn
        sh.Cells(intAMPMRow, intAMPMColumnCount).Select
        If sh.Cells(intAMPMRow, intAMPMColumnCount).Value = "AM" Then
            ' Make PM visible
            Range(Cells(intAMPMRow, intAMPMColumnCount + 2), Cells(intAMPMRow, intAMPMColumnCount + 3)).Select
            Selection.EntireColumn.Hidden = False
            ' Move date
            Range(Cells(intDateRow, intAMPMColumnCount + 2), Cells(intDateRow, intAMPMColumnCount + 2)).Value = Range(Cells(intDateRow, intAMPMColumnCount), Cells(intDateRow, intAMPMColumnCount)).Value
            Range(Cells(intDateRow, intAMPMColumnCount), Cells(intDateRow, intAMPMColumnCount)).Value = ""
            ' Center date over selection
            Range(Cells(intDateRow, intAMPMColumnCount + 2), Cells(intDateRow, intAMPMColumnCount + 3)).Select
            With Selection
                .HorizontalAlignment = xlCenterAcrossSelection
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
       
            ' Move day
            Range(Cells(intDayRow, intAMPMColumnCount + 2), Cells(intDayRow, intAMPMColumnCount + 2)).Value = Range(Cells(intDayRow, intAMPMColumnCount), Cells(intDayRow, intAMPMColumnCount)).Value
            Range(Cells(intDayRow, intAMPMColumnCount), Cells(intDayRow, intAMPMColumnCount)).Value = ""
           
            ' Adjust column width for visible columns
            Range(Cells(intAMPMRow, intAMPMColumnCount + 2), Cells(intAMPMRow, intAMPMColumnCount + 3)).Select
            Selection.ColumnWidth = 4.43
   
            ' Hide columns
            Range(Cells(intAMPMRow, intAMPMColumnCount), Cells(intAMPMRow, intAMPMColumnCount + 1)).Select
            Selection.EntireColumn.Hidden = True
           
        End If
    Next intAMPMColumnCount
   
End Sub

Public Const intDateRow As Integer = 5
Public Const intDayRow As Integer = 6
Public Const intAMPMRow As Integer = 7
Public Const intFirstAMPMColumn As Integer = 3
Public Const intLastAMPMcolumn As Integer = 57
Public Const dblColumnWidthVisible As Double = 4.43
Public Const dblColumnWidthAllVisible As Double = 3.57
1
Martin LissOlder than dirtCommented:
Thanks for that. I was having a problem with the first "AM" column in each week. I don't actually need the code because when I manually un-merged the first few columns I saw this.
Cursor_and_Microsoft_Excel.jpgIt showed that a nice trick of putting the same value in both cells had been used everywhere but in the first columns of each week. When I fix that I'm pretty sure my code will work.

I'm going out for a couple of hours.
1
Martin LissOlder than dirtCommented:
Try this update.
29080693a.xlsm
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Great JOB !!
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Zeth Larsson  Thanks for  the code  Great JOb !

Martin Working !
0
Martin LissOlder than dirtCommented:
Adriana, in the code in Module1 you find a sub called AMPM which is the code that the AM/PM/AM & PM button uses. In that code you'll find a long comment that starts with  "Note the difference between this line and the first line" followed by an If statement. I've worked on this a while longer because I didn't like the workaround code in that line. I've found a better way now and so if you like you can delete the comment and replace that If statement with

If .Cells(7, lngCol).MergeArea(1, 1) = "AM" Then

Open in new window


Here's the complete code for that sub which now includes the change.
Sub AMPM()
Dim lngCol As Long
Dim strCol As String
Dim rngToHide As Range
Dim lngLastCol As Long
Application.ScreenUpdating = False

With ActiveSheet
    lngLastCol = .UsedRange.Columns.Count
    strCol = Split(Cells(1, lngLastCol).Address, "$")(1)
    .Range("C:" & strCol).EntireColumn.Hidden = False
    
    ' Make sure columns are wide enough to show the date
    ' when it's merged companion is hidden.
    For lngCol = 3 To lngLastCol
        If .Cells(7, lngCol).Interior.Color <> vbRed Then
            .Columns(lngCol).ColumnWidth = 4.2
        End If
    Next
    
    Select Case Trim(.Shapes("cmdAMPM").TextFrame.Characters.Text)
        Case "AM"
            ' Hide PM show AM
            For lngCol = 3 To lngLastCol
                If .Cells(7, lngCol) = "PM" Then
                    If Not rngToHide Is Nothing Then
                        Set rngToHide = Union(rngToHide, Columns(lngCol))
                    Else
                        Set rngToHide = Columns(lngCol)
                    End If
                End If
            Next
            rngToHide.EntireColumn.Hidden = True
            .Shapes("cmdAMPM").TextFrame.Characters.Text = "         PM"
        Case "PM"
            ' Hide AM show PM
            For lngCol = 3 To lngLastCol
                If .Cells(7, lngCol).MergeArea(1, 1) = "AM" Then
                    If Not rngToHide Is Nothing Then
                        Set rngToHide = Union(rngToHide, Columns(lngCol))
                    Else
                        Set rngToHide = Columns(lngCol)
                    End If
                End If
            Next
            rngToHide.EntireColumn.Hidden = True
            .Shapes("cmdAMPM").TextFrame.Characters.Text = "AM and PM"
        Case Else
            ' Show both but both are already shown so just change the button text
             .Shapes("cmdAMPM").TextFrame.Characters.Text = "         AM"
           
   End Select
    
End With

Application.ScreenUpdating = True
End Sub

Open in new window

1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.