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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.