Hide Columns based on a value in a range

[craigos]

Hi There,

I have a macro that copies certain cells to another sheet based on any cell in a specific range matching a value of 3.

What I would like to do is expand that macro or call a seperate macro at the end of my code so that when the data is copied across into a sheet called "Test County", if any column D:H in the range D4:H200 does not equal the value of 3, then hide that column.

I have searched and searched and seen many similar(ish) examples but cannot find anything that I can modify to fit my requirements.

In Anticipation

Craigos

[Bob Phillips]

[vba]

With Worksheets("Test County")

For Each col In .Range("D4:H200").Columns

col.EntireColumn.Hidden = Application.CountIf(col, "<>3") > 0
Next col
End With
[/vba]

[craigos]

Hi,

Thanks for the reply, but all it is doing is hiding ALL columns D:H even when a 3 is showing in the cells within the range D4:H200.

For example Col D has a 3 in range....Col E has a 3....Cols E & F have no 3 in the range...Col H has a 3....execute Macro and all cols Hide not what it should be doing as in Hiding E & F and showing D, E and H.

Any ideas greatly welcomed

Craigos

[Bob Phillips]

My testing didn't so maybe it would be best to post your workbook.

[craigos]

Hi xld,

Thanks for your patience:

Workbook attached....

DEPT Mapping Test - Attachment.xls

Thank You

Craigos

[GTO]

Hi There,

I opened the wb. The range D4:H200 has no values at all. After unhiding the gridlines, I filled D4:H200 with all 3's. I didn't test your userform, but simply Bob's code, which of course ran thru the columns w/o hiding any. If I changed a cell to another value, then that column would be hidden when the colde is run.

Are you sure you want all 3's in a column, from top to bottom?

Mark

[craigos]

Hi GTO,

I dont want all 3's in a column as such as it will depend upon what is copied from the 'All Lists' sheet at columns T:X.

On 'All Sheets' in Col B - Local Authority - the selection is 'Branch' and there are 4 Wards, looking at Cols T:X on that sheet, the macro in the userform copies any data that has a 3 in the Cells T:X.

In the case of 'Branch' it copies 3 Wards as:

Ward1 (Copy) has a 3 in cell T4.
Ward2 has a 3 in the cell U5
Ward4 has a 3 in cell X7

The macro copies the Row across, so that when the data is presented on the 'Test County' sheet in Rows 4:7........ at D4:H7 (for this example) the result and my expectation:

D3 has a 3 in it, keep Col showing.
E5 has 3 in it, keep Col showing.
H3 has a 3 in it, keep Col showing.

As there are no 3's at all in Cols F and G , Hide those Cols.

The show / hide will be any combination of D:H depending if a 3 is in the Col range - the range changes dependant upon how many Wards are in a Local Authority that have a Priority clasification of 3 on 'All Lists' Cols T:X.

I hope that clarifies and makes sense

Thanks

Craig