MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need to compress some Auto Filter the best way?

Posted by hansoh on January 10, 2002 10:02 AM

i have a spreadsheet with hundreds of rows. at any given time, only some rows will have data in them.

i don't want to print out the whole spreadsheet if there are no values in some or most of these rows; i only want to print rows with data in there. there will be times, however, when i want to print the whole spreadsheet with ALL the rows.

in the past, i've used the AutoFilter feature. this allows me to compress the spreadsheet to show only the rows that meet a certain criteria (e.g., has data, > $0, value = 1, etc.). once filtered, i can print just the rows i want to. i want to know: is this the best way of doing this? is there a better way? please advise. thanks.

Posted by Mark W. on January 10, 2002 10:29 AM

You could also set a desired Print_Area (nt)

Posted by Mark W. on January 10, 2002 10:31 AM

Of course, this suggestion assumes that the empty rows are at the bottom (nt)

Posted by hansoh on January 10, 2002 10:44 AM

good catch...

the empty rows are all over the place. and the empty rows can change everyday.

Posted by Chris D on January 10, 2002 12:32 PM

Re: good catch...

Next time you have to autofilter, could you record a macro in relative mode ? including the print....

then next time, it will do it automatically with just one click

Make sure it's relative though, or it will always autofilter the same rows out, relative will tell it to do your blank/empty/ >$0 ones.

I used this to filter on different types of bank statement info I'd downloaded every morning.... each filter produced a sheet of different transaction types, for different members of staff to work on...


Posted by hansoh on January 10, 2002 1:19 PM

Re: good catch...

thanks for the suggestion. what i ended up doing is something a bit different.

instead of a relative macro, i 'recorded' my autofilter and print set-up settings in a couple of custom views.

although the approach is very different, i think for the purposes of this simple exercise, the results are the same. thanks.