How do I Hide/unhide columns with vba based on cell value. I have a tracker that collects numerical data twice a week and would like those columns that are filled in to be hidden once they have a value of >=0 . In the same module I would like for the upcoming dated coums to appear.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hide/unhide columns with VBA based on cell value
0
Discussion
If you provide a workbook to put the solution in we won't have to do that work and you will be sure that the solution complies with what you imagined. It's a win:win, as our friends in Beijing like to say.
"they have a value of >=0 " is insufficient, as is "upcoming dated coums ". Pease do spend a minute on a second thought. On second thought you may find that there is only one future column you need at any one time, and it should have a date of a day, week, month or year in its caption. The need for it will probably be determined by the current date and the older columns should be hidden by exactly the same rule by which the new one is added, not by >=0.
This isn't fair, Lynn, because I have programmed this sort of thing before and you haven't. But preparing the workbook in which you want the action with due care will take you to the doorstep of many details that you are better qualified to decide than any of us.
"they have a value of >=0 " is insufficient, as is "upcoming dated coums ". Pease do spend a minute on a second thought. On second thought you may find that there is only one future column you need at any one time, and it should have a date of a day, week, month or year in its caption. The need for it will probably be determined by the current date and the older columns should be hidden by exactly the same rule by which the new one is added, not by >=0.
This isn't fair, Lynn, because I have programmed this sort of thing before and you haven't. But preparing the workbook in which you want the action with due care will take you to the doorstep of many details that you are better qualified to decide than any of us.
Variatus (rep: 4889) Feb 11, '21 at 10:08 pm
Thank you for your response. I didn't quite understand your entire message. I attached the tracker I need assistance with. I need the two columns with dates within one week to show and once they are filled in then to hide and the next two to unhide, and so on. the values include the number of people trained, but accept a zero value as an entry as well.
Lynn212 (rep: 2) Feb 12, '21 at 1:32 am
Sorry for not being clear. What I mean is that I like to teach Excel but I don't like to do your work or even work for you. I saw that you didn't take care in asking your question. Instead of amending it you feed some detail in the Discussion. But such detail doesn't even include the name of the sheet on which you want to work. Clearly, you have no time to think clearly of your plan. No chance for me to teach. I hope someone else will take the job.
Variatus (rep: 4889) Feb 12, '21 at 3:23 am
Answers
0
Lynn
This line (or variants of it) would hide the second column in worksheet number 1 of your file ("NJ"):
Worksheets(1).Columns(2).Hidden = TrueChange the numbers and other columns on other sheets can be hidden or unhidden. The numeric form of the suggested code should make looping through sheets/ columns easier.
You need to do that when a condition is met (e.g. rows 3 to 11 of a given column are all non-zero).
If you have limited experience of VBA, I suggest you look in the VBA section of the Tutorials page, e.g. Loop through All Worksheets in Excel using VBA and Macros and Excel Macro Class 3 - Looping through Ranges & Working with Cells but others too.
Good luck