Auto hide Rows that are not not populated (have formulas in them) on Invoice sheet

  • I have built an Invoice worksheet that has a top portion (A1:K19) that will remain static; this is the top portion of the invoice. Below Row 19 starts the individual item lines that will be populated with what items are ordered.


    I have created a macro for the user that will automatically format the invoice into a 1 page PDF; however I now want to make it where the user can populate the line items and not have to worry about removing or hiding the empty rows left over. The line item range is (A20:J59) 40 rows. I have tried a few things, but the cells are not technically empty, they have formulas.


    Question: What type of VBA code would allow me to auto hide the empty rows that are empty (they have VLOOKUP formulas in them) in the invoice, but still show the bottom of the invoice for totals (which is part of the print area) and once exported to PDF would unhide so they user would see the invoice template in its standard format.


    Below is the current VBA script I have written.


  • Re: Auto hide Rows that are not not populated (have formulas in them) on Invoice shee


    Hello Exceldisaster,


    You probably only have to make one small change:-


    At the top and bottom of your code, remove the "For/Next" statements, select the actual range that you want to hide (excluding the totals lines) and replace it at the top with:-


    Code
    ActiveSheet.Range("A20:J59").SpecialCells(4).EntireRow.Hidden = True


    (or whatever the range is with the totals lines excluded)


    Use the same line at the bottom but change True to False and remove the SpecialCells(4) part.


    I hope that this helps.


    Cheerio,
    vcoolio.

  • Re: Auto hide Rows that are not not populated (have formulas in them) on Invoice shee


    Thanks vcoolio,


    I have it almost working. My problem now is when i run the macro it is hiding the whole range A:20-J59, how do I get it to only hide the rows that are emtpy?

  • Re: Auto hide Rows that are not not populated (have formulas in them) on Invoice shee


    Hello Exceldisaster,


    Apologies. I didn't quite follow your first post. If the User fills in, for example, line items from row 20 to row 30 and you have say rows 58 and 59 for totals, you would like to hide the remaining unused rows (31:57).


    Based on Column A, try the following lines of code:-


    Code
    ActiveSheet.Columns("A").SpecialCells(4).EntireRow.Hidden = True


    at the top and


    Code
    ActiveSheet.Columns("A").EntireRow.Hidden = False


    at the bottom.


    If its still not quite right, please upload a copy of your work book (use dummy data) so I can see exactly what needs to be done.


    Cheerio,
    vcoolio.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!