SQL and Last Cell

[jstevens]

I can run various SQL Queries to Sheet1 which return the expected results. The challenge I'm facing is that the "Last Cell" (F5>Special>Last Cell) keeps moving to the right on subsequent queries run.

Within the code I have cleared Sheet1 cells: Sheet1.Cell.Clearcontents, deleted Columns B:DZ and checked for query & existing connections (there are none).

Your thoughts are appreciated.

[HansV]

Sorry, no idea. Do you have a sample workbook?

[jstevens]

Hans,

I'm unable to post a sample workbook as there is a SQL database that the VBA code utilizes.

One thing that I observed is that the F5>Special>Last Cell returned cell address is not the same as the intersection of the last row/column used. If I save the file and run F5>Special>Last Cell again it returns the correct cell address.

[HansV]

You can reset the last cell in VBA by referring to the UsedRange property of the worksheet. For example:

Dim rng As Range
Set rng = ActiveSheet.UsedRange

You can refer to a specific sheet instead of to ActiveSheet, of course.

[jstevens]

Hans,

Your suggestion worked.

Thank you.