Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Automatically Deleting Empty Rows

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi there - I am creating an invoice template where there are multiple rows to be completed depending on the volume of work the contractor has done. On many occasions there are far too many blank rows left once the invoice is finished. Is there a formula/macro available to automatically delete empty rows when saving the invoice?

Thanks (ps I am a complete macro-beginner!)

View Answers     

Similar Excel Tutorials

How to Add, Remove, and Rearrange Columns and Rows in Excel
Adding, removing and rearranging columns is as easy as resizing. You just select your columns or rows, right click ...
Delete Rows in Excel if Completely Empty
This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of ro ...
Delete Empty or 'Broken' Named Ranges (#REF!)
This macro will delete named ranges which refer to empty or #REF! ranges. This means that if a named range has bee ...
Find the Next Blank Row with VBA Macros in Excel
Learn how to find the next empty cell in a range in Excel using VBA and Macros.  This method will skip any blanks ...

Helpful Excel Macros

Delete Rows in Excel if Completely Empty
- This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of rows, ru
Delete Blank Rows in Excel
- This is a macro which will delete blank rows in excel. This version will delete an entire row if there is a blank cell
Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete
Delete Empty Columns
- This macro will delete columns which are completely empty. This means that if there is no data within the entire column
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose

Similar Topics

I am creating an invoice with non standard rows, as in there could be 10 items nominated on the invoice or 50.

I have used data validation drop down boxes to list the items to be selected with a vlookup picking up the price of the item selected and multiplying it by the quantity.

My problem is how do I sum the totals in the invoice via a standard formula in the same spot on every sheet if I don't know how many rows are going to be on the invoice? At the moment I am leaving rows blank down the bottom of the invoice and will leave out the total if the invoice continues on to another page? Is this the best approach?

i am complete beginner.
i am currently designing an invoice in excel.
i want a macro that will automatically save an invoice worksheet from its invoice number which will clear the sheet for a new invoice with a new invoice number. the worksheet should then be saved in my documents.
how do you create a macro that automatically generate a new invoice number from the previous number. for e.g. 101 going on to 102
i have searched through the forum and found solution but it doesn't work for me.

Good afternoon,

Any help would be great. I have a good understanding of Excel however I am very new to VBA Macros.

I have created an invoice template within excel. I have Macro's within this invoice to:
- provide next invoice number upon saving current invoice
- save completed invoice to specified file
- clear template so ready to use again for next invoice number.

What I now require is code which enables me to copy certain cells upon saving each invoice and compile them into an excel table (within the same workbook but different sheet) each time the save button is clicked (once invoice has been completed)

Info I would like to copy to the table upon each completed saved invoice:
"E5 Invoice Number"
"A11 Client"
"E4 Date"
"C10:E13 Project Details"
"E46 Balance Due"

I have attached the document I am trying to make these changes to.

Many thanks in advance


Help - Save-invoice-data1.xlsThis is my first time trying to use a macro and i am running into a issue. The below macro will ignore the rows if it is completely blank with no formulas. However i have formulas in ever cell for example =IF(K21="","",VLOOKUP(K21,Data!A:B,2,0)*J21). Even though in the formula i am telling the cell to be blank the macro is not reading it as a blank. Any help would be greatly appreciated.

Sub Button2_Click()

Dim rng As Range
Dim temp As Variant
Dim i As Long
Dim a As Long
Dim rng_dest As Range

Application.ScreenUpdating = False

i = 1

Set rng_dest = Sheets("Invoice data").Range("A:R")

' Find first empty row in columns A:R on sheet Invoice data

Do Until WorksheetFunction.CountA(rng_dest.Rows(i)) = 0

i = i + 1


'Copy range B21:S37 on sheet Invoice to Variant array

Set rng = Sheets("Invoice").Range("B21:S37")

' Copy rows containing values to sheet Invoice data

For a = 1 To rng.Rows.Count

If WorksheetFunction.CountA(rng.Rows(a)) <> 0 Then

rng_dest.Rows(i).Value = rng.Rows(a).Value

i = i + 1

End If

Next a

Application.ScreenUpdating = True

I have created a new invoice template after downloading and applying the macros provided in InvoiceAutoNumber.exe (which is from Ozgrid). The Invoice is laid out in a regular way, with a large space in the middle to add items. I have 30 rows which are for inputting different items. however if the number of items should go beyond 30 there will not be enough space on the page. I need to either add extra rows, or somehow have the page/template overflow automatically onto the next page if needed. (using a new invoice for extra items that don't fit on the page is not an option).
Thanks in advance.

Hi all,
I am a novice wrt Excel. I had created a simple Invoice, to save as a
template. Would like to have the Invoice Number cell automatically in
running number, for each invoice I created. Example my first invoice will
have the number 001. Once finished and saved, opening up another new
invoice, using the template, it would now show 002, and so on.

Truly appreciate some advise to do this.

Thanks in advance.



I am trying to create a workbook where I can log what work I have done in one spreadsheet and allocate an invoice number to it. In a separate spreadsheet within the same workbook I have created an invoice template. When I enter in the invoice number into the invoice template it's not collecting the correct information.

For example, if I want the details for invoice 10 to show in the invoice template, details for invoice 19 appear.

I have used this formula:
=IFERROR(INDEX(ServiceRecord[[Invoice number]:[Date invoice issued]],SMALL(IF(ServiceRecord[[Invoice number]:[Date invoice issued]]='Invoice TEMPLATE'!$F$8,ROW(ServiceRecord[Invoice number])),ROW(1:1)),2),"")

Does someone mind having a look in to the attachment and letting me know what I have done incorrectly and how I can fix it?

I am wanting to add new information as time goes on and also to be able to put in any invoice number into the invoice template to recall information as needed.

Thank you

I have created a macro to populate an invoice with data. The number of rows of data is variable. I need to cap the bottom of the invoice with a footer that contains a formula for column totals and the final price. Attached is the sample invoice.
I need help with the code to create X number of rows containing formulas to perform on the inputted data, and to cut bottom portion footer of the invoice from the side of the sheet and paste it after the last record from within the invoice, maintaining that the number of records within the invoice will be variable. Alternatively I might not need to cut and paste the footer but just have the macro generate it at the bottom.
Both the individual rows within the invoice contain SUM formulas as well as the footer rows. Cell formatting is important too as some cells are currency, dates, and numerics, and some have borders.
Any help with this would be greatly appreciated!!

I have a invoice in excel and I want a certain cell to automatically enter
the next invoice availalbe. How can I make that happen? How will it know
what was the last invoice. I once had a PO template that was in excel and it
automatically populated the next available PO #?

Amy help will be appreciated.


Dear Friends,

This forum has came as a wonderful hand of help and I need solution to a problem of mine.

I have a sheet (Annexure 13024) with multiple rows, having information of Invoice No., Invoice Date, VSAT ID, Days etc. In the adjacent sheet the invoice template is attached. The template needs to be populated fetching the data from the rows of Sheet "Annexure 13024" in a way that an invoice is generated for data of each row. I hope I have been able to convey what I exactly want to do. Both the sheets (Annexure and Invoice Template) are in the attached "Test.xlsx"

Would be more than happy to have a solution to this.


Sorry, I didn't quite know how to word the title .

I have an Excel Invoice Template, saved as a template.

I have code that generates a new invoice number each time the template is opened in VBA Editor. The auto numbering system works great! However.... (here comes the sad part)

After I enter all the data I need on the invoice, I want to save a copy of the invoice in a different file folder, which I can no problem. The problem is, if I re-open the saved invoice, we will call it Invoice #100, excel asks me if I want to update, if I say either yes or no, the invoice number will change to Invoice #101. I'm sure you can see how this can cause a major problem if we need to compare information later on, finding the correct invoice would be almost impossible as it would not match the customers invoice number.

What I need to know is:
Is there code I can add to the existing code, to stop the increment on a saved invoice, but not on the original template?

I have gotten so much information from this site Im so glad it is here and hopefully I can help someone out in the future


Dear all,

Please I am new to this forum the site is still strange to me.

I need a macro that on invoice template will add new invoice with number MIL-EI-0001 and on, and then paste specific data of the invoice in invoice register with the invoice number hyper linked and clear specific data in the invoice template.

Best Regards,


I am looking for some suggestions on how can I possibly withdraw main data from my Invoice template (like: Invoice number, Name of the customer, total amount) in to another spreadsheet in order for me to create some sort of data base of every invoice that I send to my customers. I also wonder if some how I can auto increase my invoice number? For example if you open new invoice with number 001 so that the second one that you open will be automatically changed on 002 and so on, but with question If you reopen an existing invoice the number should not increase then.

I know that VBA can help but since I do not know anything about VBA I am looking for some help here.

Thanks in advance

hi all,
this is my excel sheet with debit and creditside
description debit credit goods received from mr a invoice no-245 $2,500 goods received from mr a invoice no-246 $4,500 goods received from mr a invoice no-247 $6,500 goods received from mr a invoice no-248 $8,500 goods received from mr a invoice no-249 $9,500 goods received from mr a invoice no-250 $10,500 goods received from mr a invoice no-251 $11,500 goods received from mr a invoice no-252 $11,500 goods received from mr a invoice no-253 $2,500 goods received from mr a invoice no-254 $400 goods received from mr a invoice no-255 $200 goods received from mr a invoice no-266 $5,000 goods received from mr a invoice no-277 $3,000 goods received from mr a invoice no-277 $8,000 goods received from mr a invoice no-277 $4,500 goods received from mr a invoice no-288 $9,500 goods received from mr a invoice no-299 $10,000 goods received from mr a invoice no-300 $11,000 goods received from mr a invoice no-320 $12,000 goods received from mr a invoice no-355 $7,000 goods received from mr a invoice no-345 $5,000 goods received from mr a invoice no-445 $1,000 goods received from mr a invoice no-545 $80,000 goods received from mr a invoice no-1000 $8,000 goods paid mr a invoice no-255 $200 goods paid from mr a invoice no-266 $5,000 goods paid from mr a invoice no-277 $3,000 goods paid from mr a invoice no-277 $8,000 goods paid from mr a invoice no-245 $2,500 goods paid from mr a invoice no-288 $9,500 goods paid from mr a invoice no-299 $10,000 goods paid from mr a invoice no-300 $11,000
i have got nearly 46000 lines like this.I need a formula or macro which could sort the debit and credit lines by invoice number either delete them or put some mark like * or any colour.for eg invoice no-245 is visible in both debit and credit side.I want the excel either to delete or colour those in both debit and credit side so that i could easy get the unpaid amount to customer.thanks for the help
thank you

I'm fairly new to Excel. I have a custom designed Invoice and want to automatically change the Invoice number each time I complete an invoice. (ie: Invoice 1200 changes to 1201). I've tried a few formulas and it doesn't do what I need. Any ideas? Thanks.

Hello everyone,

I have a journal in excel and I am trying to do a mail merge in Word creating an invoice. On column A, I have the invoice #'s, on column D I have the description, E is the rate, F the amount, G the total amount.
In one invoice I might have the 2 or 3 items for the same number. How do I do one invoice with for those two or three items considering that they are in different rows in the spreadsheet?

Thanks in advance!!

Can any body help

I have created a automatic invoice generator which will print the invoice and then clear the invoice and generate the next invoice number

The worksheet consists of 3 spreadsheets Invoice Lookup tables which store the products and clients Invoice list with get a grandtotal of invoice plus key information ie name etc

The Invoice no is situated in cells D1 to E1 but what i would like the macro to do is to copy the invoice only and save it in a directory C:\oldinvoices with a file name of the invoice no ie cells D1 - E1 this will enable me to be able to recreate an old invoice if needs be

I cannot see how this can be done and would appreciate any advise that yon can give me

I've been playing around and cannot find a formula for an invoice. What I am after is as follows:
Invoices to looking like: 08-09/01......08-09/02 etc etc. The 08-09 is the current tax year and obviously the munber after is the invoice number. Now what I am trying to do is after I've closed an invoice, say 08-09/02, then when I open up a new excel invoice, the invoice number should automatically change to 08-09/03.
Hope all this makes sense.

I prepare invoices using a template in Excel 2007.

I use VLOOKUP to fill in the data for the invoice based on a part number which is entered.

The template range is from A6 to I25. The header info for the invoice is in A4, D4 and F4.

Once the invoice is ready it is saved using a macro, which invokes the Save As option.

I would like to also have a macro to save the data on each invoice row in a separate master sheet when the file is being saved.

The macro should insert each row in the range (only rows which have a value in Col A starting from A6). Data from A4, D4 and F4 should also be inserted in the beginning of each row. When the template is used again for a fresh invoice the data should be appended to the earlier data in the master sheet.

Can somebody please help me




I'm rather new to excel and have been given the task of creating an invoice template that does certain things:

Dynamic invoice number, show date/time invoice was saved, car database for the dropdown boxes, customer information, car information, labor, parts, totals, etc.

I've completed everything else except for the last part of the project which is to take the customer's information/car information/total/description and a couple of other things and automatically have it transfer to another excel workbook took keep said information in a neat little customer database.

My question is, how would I take the information from those specific cells and have it automatically transfer/export to the other excel workbook every time I save the template/invoice for a customer? They want the DB to be organized by month if that's possible.

I have attached both excel files (the invoice template has some macros).
Cust Info- Feb..xlsx
SCC Invoice Template Example.xlsm

Thanks in advance!

I have an excel macro that I am creating for an invoice. The invoice is to ultimately create an FDF file to post to a PDF with fields.

I only can currently select all items on the same row, but for an invoice I need to be able to select multiple rows of the same column as well as multiple columns within the rows selected (ie. name box "test" =$A$13:$O$20.)

Currently I am using

"Select = Range(Selection.Rows & ":" & Selection.Rows)

Select(1, 17)"

Which will Select Row 1 (the current row which is selected) & 17 (the column of the row selected)

I am trying to create it so that I can select any and/or all of the rows and columns in name box "test".

Thank you.

Hi, I am new to Excel and was wondering whether what I am doing is possible. I have a list of invoices on a sheet (each row has data from one invoice). At the end of each row I have a PREVIEW hyperlink which opens another sheet where I have the open invoice template, ready to print. What I want is to complete the data on this template with the corresponding data from the row where I clicked PREVIEW. For example, if I preview the invoice from row2, all data in sheet2 should be pulled from sheet1, row2 and so on. Does this make sense?

Thanks for any help!

Hi All,
Forgive me as I am new to all this. I would like to know how to transfer data from an invoice to a invoice tracking (spreadsheet).(ie. customer name,invoice #, amount due). I currently have the Invoice created in Word but I can create it in Excel if easier. I would like it if once I completed the Invoice, I could click a button and it would automatically send certain data over to the spreadsheet as a new entry(row) to be able to track all invoices(paid & unpaid). Thank you for any help you can give.



a really silly question.

I am trying to make my excel worksheet automatically update the invoice number, once it has been saved. Ie the worksheet is called Order system and has three individual sheets on Order, Invoice and PO. I am trying to just get the Invoice number to automatically update, after saving as say 'Invoice 0001'.

Please help, I have tried numerous things but no such luck.


I have attached a spreadsheet to help explain what i am trying to achieve. You would need to open the spreadsheet to understand my question.

I would like excel to be able to automatically assign the "Invoice no" from the sales sheet into the stock sheets "111" and "222" as appropriate.

Also the invoice number would need to be assigned at the lowest empty stock cell.

For the solution i think the answer would not be a formula.

A solution that would be very help could be a macro where i select the invoice number and activate the macro with a shortcut when will then assign the invoice number against the items. This would be done individually per each invoice number.

This may be an unusual request, but your help on this one would be much appreciated.

I look forward to your solutions.