Unwanted empty rows in csv file

[Yamezz]

I have a macro that imports data from a .csv file that contains no 'blank' lines. My code copies some of this data to a template sheet to put it into an order that my accounting software is expecting, and then saves that template as a new .csv file.
At the end of the new .csv file I am getting multiple 'blank rows', which are not visible in Excel, but when opened in a text editor shows
,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,

I thought maybe my issue was that the template (which is cleared before each run of the code) was retaining blank rows.
To clear the previous data I use

InvoiceRows = Xero.Sheets("Xero Sales Invoice").Range("A:A").SpecialCells(xlCellTypeLastCell).Row
Xero.Sheets("Xero Sales Invoice").Range("A2", "AA" & InvoiceRows).Delete

InvoiceRows is initially returned as 25, but an immediate re-check of InvoiceRows = Xero.Sheets("Xero Sales Invoice").Range("A:A").SpecialCells(xlCellTypeLastCell).Row after clearing or deleting shows InvoiceRows to still be 25.

How do I remove these extraneous empty rows?

 

[Yamezz]

The rows are indeed being written by a loop and XeroRow is being updated after each iteration. Each time I run the macro (on a new dataset) the number of rows changes, from 1 row of data to a dozen. I put a breakpoint on this last calculation of XeroRow and it is evaluating correctly.

Adding your ResetLastCell code has fixed the problem. I added these lines myself,

tmp = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ActiveSheet.Range("A" & tmp + 1, "AA25").EntireRow.Delete

but I can see your solution is 'smart' and mine is 'dumb', or at least inflexible.

Thanks for your help and patience!

 

[Alex Blakenburg]

You're welcome. Glad we were able to come up with something that worked for you.