Adding Unnecessary Blank row on Export

[nstratton]

With .Workbooks.Open("R:\0.2.2 Procurement Dept - Shared\3.0 Groups\SQD\FTTQ Charts.xlsx")
     lngLastDataRow = .Worksheets("ChartData").Cells.SpecialCells(11).Row
     .Worksheets("ChartData").Range("A" & CStr(lngLastDataRow)).CopyFromRecordset rst
     .Worksheets("ChartData").Range("A:D").RemoveDuplicates Columns:=4

Above is a snippet of code that is being used to export data to excel 2010. The data gets into excel however it is adding a blank row above the data. I am not entirely sure why it would do that based on what I can see. I have had this issue before but cannot remember how I resolved it.

 

[arnelgp]

have you checked the recordset if indeed there are blank rows.

 

[nstratton]

When I run the query without doing the export part of it, it does not show any blank rows. The table the query is based on has no blank rows either. The fields that I am exporting all contain some type of information in them as well.

I feel like I should add as well, I had this problem before and asked it here: http://www.mrexcel.com/forum/excel-questions/887874-adding-unnecessary-blank-row-import-access-2010-windows-7-a.html
As you can see I made the change and it worked as expected. However running it now adds the blank row again.

 

[arnelgp]

so your adding your recordset to your worksheet. i tried to test this function Sheets("SHEET1").Cells.SpecialCells(11).Row, and it returns the row which have only spaces on them ( actually i only have spaces as data). so you see you have to go back rows and test for these blank cells.

 

[arnelgp]

to go back rows and testing for blank cells:

With .Workbooks.Open("R:\0.2.2 Procurement Dept - Shared\3.0 Groups\SQD\FTTQ Charts.xlsx")
    lngLastDataRow = .Worksheets("ChartData").Cells.SpecialCells(11).Row

    Do While lngLastDataRow > 1
    If Trim(Cells(lngLastDataRow, 1).Value & "") = "" Then
        lngLastDataRow = lngLastDataRow - 1
    Else
        Exit Do
    End If
    Loop

    .Worksheets("ChartData").Range("A" & CStr(lngLastDataRow)).CopyFromRecordset rst
    .Worksheets("ChartData").Range("A:D").RemoveDuplicates Columns:=4

 

[nstratton]

So something interesting happened with this. I added your code and it worked like expected with data already in the destination worksheet. This original issue was occurring when the data being exported was the first set of data (i.e. no other data was in the excel sheet previously.) The only thing on the destination sheet was headers. After adding your code and running it with only headers, the blank row still appeared. Any ideas why that would happen?