I started using the with statement to make sure I'm on the correct sheet and
avoid using the select method unless absolutely necessary. Some methods only
work with select.


The set MyRange statement would fail if LastRow or LastCol were not valid.
Private Sub cmdComplete_Click()
Dim MyRange As Range
Dim LastRow As Long
Dim LastCol As Long

with Sheets("CompleteQuote")
'There are two last rows
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
'LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column

'Not sure what this is doing?
'Sheets.Item(A1).Select

Set MyRange = .Range("A19", .Cells(LastRow, LastCol))

'LastRow.Select
'MyRange.Select
DoTheExport
Clear_Unlocked2
.Range("A19:G65536").ClearContents
txtCount = 0
end with
Sheets("QuotedPart").Activate
End Sub

Hi Joel,

I've been debugging using F8. To get the yellow highlight to move
through the code, I had to dim MyRange, LastRow and LastCol. I'm not
sure how they should be dimensioned. . .So I chose as Range for
MyRange, as Long for LastRow and LastCol.

Anyway these variables show the last row is 87. I deleted blank rows
below my data and closed the workbook--it still shows last row as 87.
When I look at the spreadsheet it looks like the last row is 68.

I suspect MyRange, LastRow and Last Column are calculating on a sheet
other than my intended sheet. I don't know how to determine where the
procedure is getting the values.

MyRange returns an error--"Run-time error '1004': Application-defined
or object-defined error"

I suspect this is because I am running a private procedure from a
button on the sheet, QuotedPart, that is manipulating a different
sheet, CompletedQuote.

Do you have any further feedback?

Thanks,

Dan

Private Sub cmdComplete_Click()
Dim MyRange As Range
Dim LastRow As Long
Dim LastCol As Long
Sheets("CompleteQuote").Activate
Sheets.Item(A1).Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

Set MyRange = Range("A19", Cells(LastRow, LastCol))

'LastRow.Select
'MyRange.Select
DoTheExport
Clear_Unlocked2
Worksheets("CompleteQuote").Range("A19:G65536").Cl earContents
txtCount = 0
Sheets("QuotedPart").Activate
End Sub