Excel automatically hiding certain columns

TanyaD · Feb 15, 2018

Hi,

Hoping you can help me. I am completely new to VBA but definitely willing to give it a try and I suspect it may be the only option to do what I need.

I have a large detailed calendar schedule for seven staff (each day has a column for each staff member). I'm hoping I can include a button for each staff member so when I press that button it only shows the columns for that staff member.

I'm guessing that to only show for staff1 the code would say something along the lines of if row 2 (the row that staff names are in) is "staff2" or "staff3" or "staff4" or "staff5" or "staff6" or "staff7" then hide. Which would then only leave Staff1's columns showing?

Greatly appreciate any help on this.

Cheers,

Tanya

TanyaD · Feb 18, 2018

Hi Fluff,

The code is below

Code:

Sub dates()
    Dim i As Long
    Dim lc As Long
    Dim dstart As Date, dend As Date
 
    ActiveSheet.Cells.EntireColumn.Hidden = False
 
    Application.ScreenUpdating = False
    lc = Cells(2, Columns.Count).End(xlToLeft).Column
    dstart = InputBox("Enter a start date, dd/mm/yyyy")
    dend = InputBox("Enter an end date, dd/mm/yyyy")
     ' Hide Selected Columns
    For i = 2 To lc
        If Cells(2, i) >= dstart And Cells(2, i) <= dend Then
            Cells(2, i).EntireColumn.Hidden = False
        Else
            Cells(2, i).EntireColumn.Hidden = True
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

TanyaD · Feb 18, 2018

Oh and I've just discovered that if I close out or cancel the pop up date input boxes instead of entering a date it returns the message run-time error '13': type mismatch

Fluff · Feb 19, 2018

This will deal with the problem of no date being entered

Code:

Sub dates()
    Dim i As Long
    Dim lc As Long
    Dim dstart As String, dend As String
 
    ActiveSheet.Cells.EntireColumn.Hidden = False
 
    Application.ScreenUpdating = False
    lc = Cells(2, Columns.Count).End(xlToLeft).Column
    dstart = InputBox("Enter a start date, dd/mm/yyyy")
    If dstart = "" Then Exit Sub
    dend = InputBox("Enter an end date, dd/mm/yyyy")
    If dend = "" Then Exit Sub
     ' Hide Selected Columns
    For i = 2 To lc
        If Cells(2, i) >= CDate(dstart) And Cells(2, i) <= CDate(dend) Then
            Cells(2, i).EntireColumn.Hidden = False
        Else
            Cells(2, i).EntireColumn.Hidden = True
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Re. merging the 2 together.
If you have it showing all staff for 12-Feb & then run the macro to show Staff 1. Do you only want to see staff 1 for the 12th?
Also if you are currently showing all columns for staff 1 & run your macro for 13th, what do you want to see?

TanyaD · Feb 20, 2018

That sorted out the glitch when no date data is entered, thanks

Re the staff & date data view combos you asked, Q1 - yes if it's showing all staff for a selected date range in the dates macro and then I run the staff macro to show for a subset of the staff members (could be just 1 staff member or a selection), I'd like to see those selected staff for the selected date range. Q2 - While it would be nice if when showing just a subset of staff & then run a selected date range in the dates macro you would see that selected date range for the subset of staff, it would be completely fine to just always start with selecting the date range (running the date macro) first and then select staff.

Hopefully that makes sense

[FONT=&quot]Thanks so much again!

Tanya[/FONT]

Fluff · Feb 20, 2018

This is somewhat more difficult than I had realised & am struggling to come up with a way of doing it.
I'll have a think on it & hopefully come up with a solution

TanyaD · Feb 20, 2018

Uh gosh so sorry, I've stumbled into macro territory that's even got an expert stumped

Would it be a lot easier to have the one 'choose date range and staff' button that has the one macro which asks for the date range and particular staff to display, and then runs that (i.e., show dates between X & Y only for staff A, B & C). Then it could just have a 'reset' or 'show all' button to go back to showing all dates and all staff?

Fluff · Feb 21, 2018

How about

Code:

Sub HideStaffDate()
   Dim Ans As Variant
   Dim Dstart As String, Dend As String
   Dim Cnt  As Long

   Ans = InputBox("Please enter staff member")
   If Ans = "" Then Exit Sub
   Dstart = InputBox("Enter a start date, dd/mm/yyyy")
   If Dstart = "" Then Exit Sub
   Dend = InputBox("Enter an end date, dd/mm/yyyy")
   If Dend = "" Then Exit Sub

   Sheets("Staff").UsedRange.Offset(, 1).EntireColumn.Hidden = False

   For Cnt = 2 To Cells(2, Columns.Count).End(xlToLeft).Column
      If (Cells(2, Cnt) < CDate(Dstart) Or Cells(2, Cnt) > CDate(Dend)) Or InStr(1, Ans, Cells(3, Cnt), vbTextCompare) = 0 Then
         Columns(Cnt).Hidden = True
      End If
   Next Cnt

End Sub

TanyaD · Feb 22, 2018

Sadly that's not working, it's hiding all the columns?

Dossfm0q · Feb 22, 2018

Good Day
Please Click XL File
with merge code

Fluff · Feb 22, 2018

@Dossfm0q
Please post the code you have used to the thread, rather than just posting a link to the file, as this is against board policy.
Thank you

Excel automatically hiding certain columns

TanyaD

New Member

TanyaD

New Member

Excel Facts

TanyaD

New Member

Fluff

MrExcel MVP, Moderator

TanyaD

New Member

Fluff

MrExcel MVP, Moderator

TanyaD

New Member

Fluff

MrExcel MVP, Moderator

TanyaD

New Member

Dossfm0q

Banned User

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Excel automatically hiding certain columns

New Member

New Member

Excel Facts

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

Banned User

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock