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?

 

[Alex Blakenburg]

After you cleared the data try adding these lines.

' Call UsedRange without activating the sheet
Dim tmp As String
tmp = Xero.Sheets("Xero Sales Invoice").UsedRange.Address

 

[MARK858]

SpecialCells(xlCellTypeLastCell) has a habit of retaining a "memory" of it's previous cell until it is saved, closed and reopened, so it still showing 25 would be no surprise.

Alex's code in the previous post might reset it (please try it first), if you still have an issue after trying it...

What do

Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row

and

Cells(Rows.count, "A").End(xlUp).Row

return?

Also if you put the formula

=UNICODE(A3)

in a spare column and drag down does it return any numbers? If yes what numbers?

 

[Yamezz]

With the following snippets of code

InvoiceRows = Xero.Sheets("Xero Journal").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Xero.Sheets("Xero Sales Invoice").Range("A2", "AA" & InvoiceRows).Delete

tmp = Xero.Sheets("Xero Sales Invoice").UsedRange.Address
Mark1 = Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row
Mark2 = Cells(Rows.Count, "A").End(xlUp).Row

InvoiceRows evaluates to 4, tmp to "$A$1:$AA$25", Mark1 to 1 and Mark2 to 1
The unicode test shows #VALUE! in all cells I dragged it to (row 32)

 

[MARK858]

So with the code you posted in post number 4 (ran on a copy of your workbook) are you still getting the below when you save to your 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
,,,,,,,,,,,,,,,,,,,,,,,,,,

Btw,

Xero.Sheets("Xero Journal").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

is not the same as

Xero.Sheets("Xero Journal").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row

As it doesn't specify the Values part, so it will pick up on any cells with empty strings i.e. formulas returning "" even if you have done a PasteSpecial.Value (and Find retains a memory of that criteria).

longhand version would be

Xero.Sheets("Xero Journal").Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[Alex Blakenburg]

You are mixing and matching.
After doing your delete, all you should need to do is get Excel to reset the end of the used range.
There are 2 ways of doing that
• Save the workbook OR
• In VBA execute any line of code that includes the use of UsedRange.
Mark's code is returning Row 1 Column 1 which proves that the UsedRange line worked and reset the end of the sheet.
You need to keep the tmp = line and remove the Mark lines which are only there to help you debug the issue and have done their job in showing you that the sheet has been reset to A1.

If after this you still get blank lines in your csv file it is becuase you are copying in the blank rows when you populate the Template.
So the next step is to rerun your macro to create the csv file and see if resetting the template fixed it.

Oops crossed over with Mark but I don't think we conflict

 

[Yamezz]

I still get the blank rows in the .csv file. I used the following code, with checks for the different method of getting the row number.

InvoiceRows = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Xero.Sheets("Xero Sales Invoice").Range("A2", "AA" & InvoiceRows).Delete
tmp = Xero.Sheets("Xero Sales Invoice").UsedRange.Address
Dim IR1 As Long, IR2 As Long, IR3 As Long, IR4 As Long
IR1 = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
IR2 = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
IR3 = Xero.Sheets("Xero Sales Invoice").Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row
IR4 = Xero.Sheets("Xero Sales Invoice").Cells(Rows.Count, "A").End(xlUp).Row
MsgBox (IR1)
MsgBox (IR2)
MsgBox (IR3)
MsgBox (IR4)

All four methods correctly identified the last row being 1 after the delete.
For some reason I decided to use XeroRow instead of InvoiceRows as the row variable later on, but XeroRow evaluates correctly to 1 after the template is cleared.
The 'blank' template is then populated line by line with code like this:

Xero.Sheets("Xero Sales Invoice").Range("A" & XeroRow + 1) = "Daily Sales Shopify"
Xero.Sheets("Xero Sales Invoice").Range("K" & XeroRow + 1) = "S" & Format(ReportDate, "yyyymmdd")
Xero.Sheets("Xero Sales Invoice").Range("M" & XeroRow + 1) = strReportDate
Xero.Sheets("Xero Sales Invoice").Range("N" & XeroRow + 1) = "'" & strReportDate
Xero.Sheets("Xero Sales Invoice").Range("O" & XeroRow + 1) = SKU
Xero.Sheets("Xero Sales Invoice").Range("Q" & XeroRow + 1) = Qty
Xero.Sheets("Xero Sales Invoice").Range("R" & XeroRow + 1) = DUP
Xero.Sheets("Xero Sales Invoice").Range("P" & XeroRow + 1) = Desc
Xero.Sheets("Xero Sales Invoice").Range("T" & XeroRow + 1) = AccCode
Xero.Sheets("Xero Sales Invoice").Range("U" & XeroRow + 1) = TaxType

The template is then exported via

Xero.Sheets("Xero Sales Invoice").Copy
ActiveWorkbook.SaveAs Filename:="Invoice S" & Format(ReportDate, "yyyymmdd"), FileFormat:=xlCSV, CreateBackup:=True
ActiveWorkbook.Close

The problem seems not in the clearing of the template. The extra rows must be added after the data entry somehow, but when I check XeroRow again after finishing the data entry, it evaluates correctly, yet the .csv file that originates from the template consistently contains extra blank rows up to 25 rows total. And when running the code again on the next batch of data, the initial row count on Xero Sales Invoice shows 25 again.
Strangely, this does not occur when using the same code to export .csv files of the Bills and Journals.

And If I manually check XeroRow after the code has run and the .csv files have been exported I get the correct value

Sub testAgain()
Dim Xero As Workbook, XeroRow As Long
Set Xero = ThisWorkbook
    XeroRow = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
MsgBox (XeroRow)
End Sub

 

[Alex Blakenburg]

If you put a breakpoint on the Save As line and on the activeworkbook / worksheet type Ctrl+End, where does it take you ?
(to the end of the data or past there).
If it takes you past the end of the data.
What happens if you add these 2 lines after the .Copy and Before the SaveAs ?
(try Ctrl+End again after these)

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
tmp = Application.ActiveSheet.UsedRange.Address

 

[Yamezz]

Alex, the first CTRL+End takes me to row 25, when there is just one header row and two rows of data.

Adding the two lines also results in row 25, and tmp evaluates to "$A$1:$AA$25"

 

[Alex Blakenburg]

It looks like you are writing the rows using a Loop.
Assuming you are incrementing XeroRow each time you run through the loop what is its value when you exit the l

Another thing you can try is after the line:

Xero.Sheets("Xero Sales Invoice").Copy

put the line

Call ResetLastCell

Copy the below into a standard module and run your code and see if that fixes it.

Sub ResetLastCell()

Dim lLastRow As Long, lLastColumn As Long
Dim lRealLastRow As Long, lRealLastColumn As Long

' Find last row,column based on special cells method
With Range("A1").SpecialCells(xlCellTypeLastCell)
    lLastRow = .Row
    lLastColumn = .Column
End With

' Find backwards from A1 the last non-blank row
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , _
    xlByRows, xlPrevious).Row

' Find backwards from A1 the last non-blank column
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , _
    xlByColumns, xlPrevious).Column
   
'Delete from the row after the real last row to the last row
'per special cells method
If lRealLastRow < lLastRow Then
    Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
End If

'Delete from the column after the real last column to
'the last column per special cells method
If lRealLastColumn < lLastColumn Then
    Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn)).EntireColumn.Delete
End If

ActiveSheet.UsedRange  'Resets last cell
   
End Sub