PDA

View Full Version : Hide Columns based on a value in a range



craigos
03-01-2012, 01:31 PM
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
03-01-2012, 06:19 PM
With Worksheets("Test County")

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

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

craigos
03-04-2012, 10:47 AM
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
03-04-2012, 04:42 PM
My testing didn't so maybe it would be best to post your workbook.

craigos
03-06-2012, 12:16 AM
Hi xld,

Thanks for your patience:

Workbook attached....

7597

Thank You

Craigos

GTO
03-06-2012, 01:10 AM
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
03-10-2012, 04:02 AM
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