Hiding rows based on multiple calculations

**carlito_1985** · 02-24-2010, 06:57 PM

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

**JBeaucaire** · 02-24-2010, 07:28 PM

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.

Hiding rows based on multiple calculations

LinkBack

Thread Tools

Rate This Thread

Display

Hiding rows based on multiple calculations

Re: Hiding rows based on multiple calculations

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions