Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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 ...
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 ...
Automatically Shade Every Other Row When You Add Data in Excel
How to make Excel automatically add row shading to every other row when you add data to the spreadsheet. This does ...
How to Resize Rows and Columns in Excel Quickly
Resizing rows and columns in Excel is an easy process. Simply left click in between the columns and drag the mouse ...

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.

Hi, i have attached a file. there are several invoices separated by empty rows. when i go to print on a single invoice on A4 paper then it makes out of order as invoice size are not same in rows, I want such code to print one empty row to next empty row on a page, then again next empty row to another next empty row on a another page, this will continue to last invoice in this sheet.. for example u can see the attachement.

Hi have a file of customers' orders laid out with multiple rows eg:

Item line in Col1, Invoice number in Col2
Col 1 Col 2
(Row A) Shirt 1 Invoice 34
Row B) Shirt 2 Invoice 34
(Row C) Shirt 3 Invoice 34
(Row D) Embroidery Invoice 34

I want to (green) highlight the rows with the same invoice # if any of the rows contain the word "embroidery". In the above example all four rows would be highlighted. with the end result being we can identify all invoices in the workfile that refer to an embroidered order.
Any help greatly appreciated.

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


I am using Excel 2003. I have a worksheet called "RawData" which has a continuous list of invoices, I need to separate off the first invoice from the rest of the invoices into a new worksheet and call it "sheet1". The number of rows from cell A1 at the top left corner varies and is never constant from invoice to invoice however the last row of the invoice I need to move always includes the text string "NET PAYABLE TO". So I need to copy all rows from "A1" to the row that has the text to a new worksheet called "sheet1", I then need to delete those rows only from the original "RawData" worksheet, leaving the remaining invoices in "RawData" intact. If it is helpful to you, the first row on every invoice has the text string "TAX INVOICE". There are no empty rows between the "NET PAYABLE TO" row at the end of an invoice and the "TAX INVOICE" row at the start of the next invoice, so it is literally a continuous list.

Can this routine then be continued on all the other invoices in "RawData", copying them to new worksheets in the same workbook "sheet2","sheet3" etc until there is no data left in RawData, bearing in mind that there may be 10 invoices or 100 invoices in the "RawData" sheet.

I hope I explained this OK, I am only just dipping my toe in the water in Excel programming and I am discovering that Excel can be amazingly the right hands!

Thanks for any assistance offered, I have spent hours looking at different code examples on the net and attempting to make them work for me but to no avail.

Regards Andy
Cairns, Australia

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 a macro that runs on a selected sheet. It looks for the next available row on 'invoice and inserts the rows of data. I would like it to pop up a message on the active sheet to say the invoice is full if it tries to put the data any row under row 33 and if possible not paste the data.
Can this be done?
ie get the macro to count the empty cells on sheet invoice A18 to A33.
If the cells with data in column A in the selected sheet is greater then do not run the macro.
Thanks, vac

Dim ws As Worksheet

'For Each ws In ThisWorkbook.Sheets
Set ws = ActiveSheet
  'If ws.Name  ("Invoice") Then
    On Error Resume Next
    With ws
      .Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
      With .Columns("A").CurrentRegion
        .Offset(2, 0).Resize(.Rows.Count - 1, 3).SpecialCells(xlCellTypeVisible).Copy _
          Sheets("Invoice").Cells(Rows.Count, "A").End(xlUp)(2)
      End With
      .Cells.Rows.Hidden = False
    End With
  'End If
'Next ws

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 the following kind of format in excel sheet

Order and Invoice Detail Order In invoice Balance 06-Feb-08 PO# 011 100 0 100 31-Mar-08 Invoice 120 0 100 0 Material Detail in hand invoice Balance 28-Feb-08 95 0 95 01-Mar-08 5 100 31-Mar-08 Invoice 120 0 100 0 Finished Goods in hand invoice Balance 18-Mar-09 95 0 95 25-Mar-09 5 100 31-Mar-08 Invoice 120 0 100 0

As you can see in the above three tables that invoice # 120 is subtracted.
This is only a short view of tables i have lot of tables of this where i copy and paste the invoice manaually which takes a lot of time.

i want that when i subtract the invoice from the "order and invoice detail" table i will automatically appear and subtract the invoice from the other tables.


i have these tables in the same columns.
Please help or suggest me another format of doing this quickly.


Hello Guys,

Can anyone help me to write the VBA code for the following requirement?

I have Master Workbook and Invoice Template

In Master Workbook the Invoices which already exist will be mentioned as "YES" (In Invoice Issuance column of Master Workbook)

I will open the Invoice Template and when i will run the Macro It should refer to the Master Workbook and need to see which are the cells blank (In Invoice Issuance Column ), If any cells are blank it should copy the data from Master Workbook to invoice template and update it and save the Invoice template sheet as a Workbook.

If anyone did not able to understand the problem kindly let me know.

If anyone needs the workbooks for reference i can upload it....
Can any one tel me how to attach the Xls files.


Vinayak H

I have a spreadsheet with multiple rows of client/customer information. The number of rows varies on a day-to-day basis from 25 to 200 rows. I have a single invoice template that requires to be printed on a daily basis. I don't want to create a worksheet for each client as this will take too much time.

I need to create a print macro for the template that will print an invoice for each client?

My client information is layed out as follows (for example):
Row 1 Name: Smith Amount owed: $20.85
Row 2 Name: Adam Amount owed: $28.34
Row 3 Name: Toms Amount owed: $26.38
and so on

In the Template, I want to print an invoice for each client. So invoice 1 is Smith and amount owed is $20.85; invoice 2 is Adam and amount owed is $28.34; invoice 3 is Toms and amount owed is $26.38 and so on.

So when printing the template, I need the value in selected cells to update to new client information each time.

I hope this explains what I am trying to accomplish. Any help is 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


I have a problem with summing in VBA.
the part of the VBA code is :
in column "B" I have invoices numbers and in column "D" I have value of the invoices.
after the above code I need to write the code which sums the value of the invoices.
Unfortunatelly I can't use "range("B1").end(xlDown).offset(1,3).Formula ="=sum(R[-1]C:R2C) because :
1. previous part of the code of my macro divides all invoices into clients (in Column A there is the client) inserting 2 empty rows below last invoice for the client - for example:
- first invoice for Client A is in row 2, last one is in row 5
- there is two empty rows
- first invoice for Client B is in row 8, last in row 10
- first invoice for Client C is in row 13, last in row 21 , ect.
in this example formula =sum(R[-1]C:R2C) would work only for Client A but for others not
2. there will be different number of invoices (and rows) for each clients

is there any method to sum the value of the invoices for each Clients using VBA code ?

I hope I described it quite clearly
would anybody help Please ?

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,

Good day,
I am preparing a new invoice template for my personal use. Everything is fine except for two issues:
1- I created a module "spellnumber" but pop-ups keep showing with active x warnings. Is there a way to get rid of the warning and keep the macro running without anyone using the template noticing that there is a macro.
2- when I insert extra rows in my invoice, it breaks to two pages looking unprofessional. how do i fix that."for example, if the invoice was extended to two pages, i want the heading and first few rows showing on both printouts, plus pages numbers so customers can tell the pages link each other. e.g: 1 of 2, and 2 of 2"

Thanks in advance.



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!!