Auto Hide & Unhide Rows/Create Lists Based Off Value

[mstark17]

So I'm using a formula that find invoices that meet a certain requirement. The problem is that the lookup formula requires the uses of 100 rows. So I was thinking of using a macro that auto hides blank cells based off their value to make a neat looking list. The rules for it are pretty simple:
- If a cell in column A = "" then hide row
- If a cell in column A<>"" then show row


If you think it would be easier to generate a list instead then the formula I'm using for it is:
AND($C6<>"", $J6>=29)

I've attached a doc to better show what I'm trying to do. Any help would be appreciated! Thanks

[cubangt]

its def doable, but how would you want to handle the empty rows between the sections?

If that number of rows for each vendor is not always the same, then some calculations(logic) will need to be done to determine the last row for a vendor, so that your total and space between vendors stays there

I think you would want to loop thru specific ranges within column A and then call .EntireRow.Hidden = True to set those that are empty to hidden.

[cubangt]

Here is a quick sample of how you can do it on your sheet, this works for the first vendor and needs to be adjusted to accommodate how you determine each vendor set.

Please Login or Register  to view this content.

The only thing with this logic is that as its hiding the rows, the row count is affected and doesnt make it past the last row with data. but its something to look at and try to use and adjust for your needs.

[mstark17]

I was thinking of just having multiple subs for each vendor in my example and just calling them all with a single macro. So the first section would only look through rows 1-150 and the next function would be 155-250, ect. As for the function since the formula is just set to blank if nothing is found the I was thinking of just having the VBA go through each row and checking if cell A in that row is blank and then hide it or unhide it if not. Coding wise I think it would be pretty simple. Only issue is I'm not good at coding.

[mstark17]

I see what you're talking about. While I can't write code, I do understand it. Would it be possible to just make it a loop function so it loops through rows 4 to 24 until there are no blank cells? Also it can't check hidden rows for new entries so maybe we could try having the first command be unhide rows 4:24 then loop through them? Thanks

[cubangt]

if you know the rows will be constant, then yes you can change that to just loop thru rows 4 thru 24 to hide and to unhide same thing..

To unhide any hidden rows from 4 to 24

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[mstark17]

Is it possible to tell it to stop at row 24? not count 24 rows?

[cubangt]

Not sure i understand your question, the above will only loop thru rows 4 until 24 and stop, it wont go any further past 24.

[mstark17]

That's fine, I messed around with it a bit and got it to work. Thanks