Gathering scattered records in excel-Vba

Question

I queried my excel sheet using two queries having the same logic but different syntax.

rs.Open "SELECT * FROM [Sheet1$] WHERE [Sheet1$].ID NOT IN(SELECT [Sheet2$].ID FROM [Sheet2$] WHERE [Sheet2$].[ID] IS NOT NULL)", cn, adOpenKeyset, adLockReadOnly
rs.Open "SELECT * FROM [Sheet1$] LEFT JOIN [Sheet2$] ON [Sheet1$].ID=[Sheet2$].ID  WHERE [Sheet2$].CUSIP IS NULL ", cn, adOpenKeyset, adLockReadOnly
With Worksheets("Sheet3$")
    .Range("A2:H2" & lastrow).Cells.ClearContents
    .Cells(2, 1).CopyFromRecordset rs
End With

I want to use query 2 and Now I see that the records from query 2 are scattered all over the sheet like, the first instance of a record I see is on 26000th row. How can I bring all these records together?

score 1 · Accepted Answer · answered Mar 26 '15 at 23:15

I think your second query lack another command. Something like AND [Sheet1$].ID IS NOT NULL.
It is not really showing in random, it just doesn't skip the NULL values in [Sheet1$].

Try it like this:

rs.Open "SELECT * " & _
        "FROM [Sheet1$] " & _
        "LEFT JOIN [Sheet2$] " & _
        "ON [Sheet1$].ID=[Sheet2$].ID " & _
        "WHERE [Sheet2$].ID IS NULL " & _
        "AND [Sheet1$].ID IS NOT NULL;", _
        cn, adOpenKeyset, adLockReadOnly

You might also want to check this link out.

Gathering scattered records in excel-Vba

1 Answers1