Hide/Unhide

[jarhead58]

Good morning!!

To start, I understand that columns can be hidden/unhidden via the Format function on the toolbar. What I need to have happen is to use IsDate to search F2:F40 for a date, none particular, but a date. If not, in the row containing the False return, look at the contents in column A, Match it in Sheet2, whatever column it is in, and hide that column. If it does, just fall through and continue. Let me know if I can detail it better than below;

Sheet1

A B C D E F
[TABLE="width: 300, align: center"]
<tbody>[TR]
[TD]Text1 [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11/7/19[/TD]
[/TR]
[TR]
[TD]Text2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Text3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11/7/19[/TD]
[/TR]
[TR]
[TD]Text4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11/7/19[/TD]
[/TR]
[TR]
[TD]Text5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11/7/19[/TD]
[/TR]
[TR]
[TD]Text6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11/7/19[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

A B C D E F [TABLE="width: 300, align: center"]
<tbody>[TR]
[TD] [/TD]
[TD]Text1[/TD]
[TD]Text2[/TD]
[TD]Text3[/TD]
[TD]Text4[/TD]
[TD]Text5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jarhead58]

A small tweak I forgot to mention. When the book opens again, if text3 now has a date, I was hoping it would unhide. How hard would that be?

 

[Fluff]

You could use the workbook open event like

Private Sub Workbook_Open()
    Dim Cl As Range, Fnd As Range
    Sheets("Sheet2").Columns.Hidden = False
    With Sheets("Sheet1")
        For Each Cl In .Range("F2", .Range("F" & Rows.Count).End(xlUp))
            If Not IsDate(Cl.Value) Then
                Set Fnd = Sheets("sheet2").Rows(1).Find(Cl.Offset(, -5).Value, , , xlWhole, , , False, , False)
                If Not Fnd Is Nothing Then Fnd.EntireColumn.Hidden = True
            End If
        Next Cl
    End With
End Sub

This code needs to go in the "ThisWorkbook" module

 

[jarhead58]

You could use the workbook open event like

Private Sub Workbook_Open()
    Dim Cl As Range, Fnd As Range
    Sheets("Sheet2").Columns.Hidden = False
    With Sheets("Sheet1")
        For Each Cl In .Range("F2", .Range("F" & Rows.Count).End(xlUp))
            If Not IsDate(Cl.Value) Then
                Set Fnd = Sheets("sheet2").Rows(1).Find(Cl.Offset(, -5).Value, , , xlWhole, , , False, , False)
                If Not Fnd Is Nothing Then Fnd.EntireColumn.Hidden = True
            End If
        Next Cl
    End With
End Sub

This code needs to go in the "ThisWorkbook" module

Still doesn't unhide the hid column on reopen with date saved in the x cell

 

[Fluff]

It should unhide all the columns and then re-hide those that don't have a date in col F.
Step through the code using F8, then when the With line is highlighted yellow, have a look at sheet 2. Are all the columns visible?

 

[jarhead58]

It should unhide all the columns and then re-hide those that don't have a date in col F.
Step through the code using F8, then when the With line is highlighted yellow, have a look at sheet 2. Are all the columns visible?

No maam! I have F2:F6 with dates but text3, column(C) is still hid!

 

[jarhead58]

I have verified that it is in the "Thisworkbook" module.

Private Sub Workbook_Open() Dim Cl As Range, Fnd As Range
    With Sheets("Sheet1")
        For Each Cl In .Range("F2", .Range("F" & Rows.Count).End(xlUp))
            If Not IsDate(Cl.Value) Then
                Set Fnd = Sheets("sheet2").Rows(1).Find(Cl.Offset(, -5).Value, , , xlWhole, , , False, , False)
                If Not Fnd Is Nothing Then Fnd.EntireColumn.Hidden = True
            End If
        Next Cl
    End With
End Sub

 

[Fluff]

You're missing this line

Sheets("Sheet2").Columns.Hidden = False

As for

No maam!

The last time I checked I was definitely a sir

 

[jarhead58]

You're missing this line

Sheets("Sheet2").Columns.Hidden = False

As for The last time I checked I was definitely a sir

Open mouth, insert foot lmao! No offense but your handle made me "assume", stupid word lol! My
sincere apologies sir!
I had to step away from the laptop but I will let you know how it goes! Not sure how that happened, just copied and pasted.

 

[jarhead58]

That did the trick!! Thank you again!!

 

[Fluff]

Glad to help & thanks for the feedback