CODEWITHSUNDEEP
excel
Filmore34
Filmore34

Reputation: 68

Hiding columns if cell in column equeals Sat or Sun

I have a production workbook that has a tab for each month of the year and a YTD summary tab. Each monthly worksheet has the rows that show the different events or actions that make up a workday and the columns have the date as the header row. We do not work on Saturday or Sunday but those days appear on each of the spreadsheets. I am wanting to know how can i create a macro or VBA code to be able to automatically hide columns if the cell in that columns contains Sat or Sun. As there are multiple Saturdays and Sundays in a month it would hide multiple columns on each spreadsheet. Row 34 is the day of wek on each of the spreadsheets, utilizing a three digit day of week - Mon, Tue, Wed, etc, so i need something that says if that cell is Sat or Sun - hide that column and my columns go from B to AG. We have one of these spreadsheets for each of our over 50 workers so I could go in and manually hide them but that would take a lot of time and I know that there is a more efficient solution.

Thanks

Upvotes: 1

Views: 1386

Answers (2)

Ryguydg
Ryguydg

Reputation: 103

Tested this based on your description above. Should work as expected. 

Sub Hide_Columns_Based_On_Criteria()
Dim iCntr As Long, WS_Count As Integer, I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count

    ActiveWorkbook.Worksheets(I).Select
    For iCntr = 2 To 33 Step 1

        If Cells(34, iCntr) = "Sat" Then
            Columns(iCntr).EntireColumn.Hidden = True
        End If

        If Cells(34, iCntr) = "Sun" Then
            Columns(iCntr).EntireColumn.Hidden = True
        End If
    Next iCntr
Next I

End Sub

You can make adjustments to iCntr if the column range changes from B to AG

For iCntr = 2 To 33 Step 1

Upvotes: 0

rpalo
rpalo

Reputation: 243

I'm assuming you know how to set up and run VBA code as a macro? If not, see this: http://office.microsoft.com/en-us/excel-help/create-or-delete-a-macro-HP010342374.aspx#BMcreatemacrovba

Try this for the code.

Sub HideWeekends()
Dim i as integer
Dim sht as Worksheet

For each sht in ActiveWorkbook
    For i = 1 to 31
        If sht.Cells(34,i) = "Sat" Or "Sun" then
           sht.Cells(34,1).EntireColumn.Hidden = True
        End if
    Next
Next
End Sub

You will have to modify this to match your spreadsheet. If the first day of the month is actually in column C instead of column A, then change

sht.Cells(34,i)

to

sht.Cells(34,i+2)

and so on.

I'm also doing this on a computer without excel, so let me know how that works and I can work with you!

Upvotes: 0

Related Questions