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]

Assuming it was on this line

Set Fnd = Sheets("[COLOR=#ff0000]sheet2[/COLOR]").Rows(1).Find(Cl.Offset(, -5).Value, , , xlWhole, , , False, , False)

Change the value in red to match your actual sheet name

That's the name I use just a test workbook. I'll implement it once I get it working.

 

[Fluff]

Was it that line of code that gave the Run time error 9 Subscript out of range error?

 

[jarhead58]

Was it that line of code that gave the Run time error 9 Subscript out of range error?

Yep

 

[Fluff]

In that case there is no sheet called "Sheet2" in the active workbook when you ran the code.

 

[jarhead58]

In that case there is no sheet called "Sheet2" in the active workbook when you ran the code.

Well that was a duh moment!! Evidently, the add-in I used to post, brought up a module that covered my existing module. I was pasting it into a different WB module. Ok, it has one hiccup and it may just be the way I communicated it. The column that I need hid is the matching sheet2 column; sheet1 text3(A4) to sheet2 column(D), so column(D) would be hidden. It hid column(F) text5 for some reason.

 

[Fluff]

With data like

Book1
	A	B	C	D	E	F
2	Text1					11/07/2019
3	Text2					12/07/2019
4	Text3					x
5	Text4					11/07/2019
6	Text5					11/07/2019
7	Text6					11/07/2019
Sheet1

I get

Book1
	A	B	D	E	F
1	Text1	Text2	Text4	Text5	Text6
Sheet2

If you are getting something different, check you data is correct & that your dates are real dates, not text

 

[jarhead58]

With data like

	A	B	C	D	E	F
Text1
Text2
Text3	x
Text4
Text5
Text6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11/07/2019[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/07/2019[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11/07/2019[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11/07/2019[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11/07/2019[/TD]

</tbody>

Sheet1​

I get

	A	B	D	E	F
Text1	Text2	Text4	Text5	Text6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet2​

If you are getting something different, check you data is correct & that your dates are real dates, not text

Evidently it didn't like me just running the module. I reset the columns, saved the WB and then reopened it. It got the right column but also hid col(F) thru (AM)!

 

[Fluff]

That's because F5 to F40 were not a date.
If you want to limit it to the used range try

    For Each Cl In Range("F2", Range("F" & Rows.Count).End(xlUp))

 

[jarhead58]

That's because F5 to F40 were not a date.
If you want to limit it to the used range try

    For Each Cl In Range("F2", Range("F" & Rows.Count).End(xlUp))

Ah, gotcha, makes sense to this knucklehead lol!! Works fine! I really appreciate all of the help Fluff!!

 

[Fluff]

My pleasure & thanks for the feedback.