Hi all,
I have a spreadsheet which I need to write a macro to hide rows based on certain cells being empty or certain cells being full.
I need the macro to look for two things in each row.
The first thing is whether the cells within columns K, L, M and N are empty. If yes, hide row.
If not the other thing to look for is whether cells within column N are full. If yes, hide row.
Is it possible to write a macro for this which will loop throughout the entire sheet of around 600-700 + rows?
I also need it to start at row 2 as row 1 has all the header information.
Thanks heaps,
Andrew
You can do this on-the-fly yourself with no programming using a KEY column and the Autofilter. Have you tried that?
In an empty column, in Z1 put "Key" and in Z2 put this formula:
=IF(COUNTA(K2:N2)>0, "Show", IF(N2="", "Show", "Hide"))
...you should receive a "Show" if the row should remain visible.
Copy Z2 down as far as needed.
Then click on Z1 and Data > Filter > Autofilter to turn on the filter in that column.
Use the drop down and select "Show" and you have the layout you want.
You may have to tweak the formula based on how the "blanks" in K:N appear...but you should be able to get it. If not, post up a sample workbook so we can see your data layout.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks