Trouble exporting using Chip Pearson's export

[dan dungan]

Hi,

I'm using Excel 2000 running on Windows XP professional.

Private Sub cmdComplete_Click()
Worksheets("CompleteQuote").Range("A19:I19").End(xlDown).Select
DoTheExport
Clear_Unlocked2
Worksheets("CompleteQuote").Range("A19:G65536").ClearContents
txtCount = 0
Sheets("QuotedPart").Activate
End Sub

DoTheExport is Chip's code which works great if I manually make the
selection.

The range I want to select start on row 19 and can be of variable
length. Is there a way to select a variable range?

Thanks,

Dan Dungan

 

[Don Guillett]

Post ONLY in the group where you want an answer. Otherwise, you waste a lot
of responders time.

 

[Guest]

The range you chose is a string. Any stement that works on a string will
also work inside a range.

Here are a lot of choices that will replace "Range("A19:I19")"

1) set MyRange = Range("A19:A119")
MyRange.End(XLUP).select

2) My1stRow = 2
LastRow = 10
My1stCol = "A"
Mylastcol = "E"
Range(My1stcol & My1stRow & ":" & MylastCol & LastRow)

3) LastRow = 10
Range("A19:A" & Lastrow)

4) Range uses letters for columns. when you have numbers use cells
Range("A19",cells(lastrow,"A"))

5) Range(cells(19,1),cells(19,9))

Hope this give you some ideas.

 

[dan dungan]

Thanks for your response, Joel.

I'm not clear how to implement your examples.

I'm looking for a way to describe a variable range. These examples
seem to show the range limits defined.

Allow me to describe some context:

Customer service agents use this spreadsheet to calculate a quote for
an electrical part number.

If the customer has more than one part number listed in the quote
request, our agent adds the price for each part quoted.

The quotes are appended to this sheet, CompleteQuote.

I don't want to append the first 18 rows because they contain header
information and formulas.

But I don't know what the last row will be until the service agent
clicks the Completed button.

So I have to questions:

1. How can I determine which cells have data and define the range I
want to select?

2. When I select the range manually, the range color changes to a blue
tint. When I'm debugging, I was looking for this blue tint to
determine the correct range was selected. Is there a better way to
verify what range is selected?

Thanks,

Dan

 

[Guest]

1. How can I determine which cells have data and define the range I
want to select?

It depends if all the cells contain data (no blank row or columns).

To get the last row the best way is to go to the last row of the spreadsheet
and search up until you find the first cell with data. This makes sure you
include all the blank rows or cells. Use a column that has data in every
row. I chose column A

rows.count is a constant in excel which is the last row = 65536 for excel
2003.
Xlup says to searcxh up until a non-blank cell is found

LastRow = cells(Rows.Count,"A").end(xlup).Row

Likewise for columns (last column is 256)
LastCol = cells(1,Columns.Count).end(xltoleft).Column

To set the entire range of cells

set MyRange = range("A19",cells(LastRow,LastCol))

--------------------------------------------------------------------------------------------
2. When I select the range manually, the range color changes to a blue
tint. When I'm debugging, I was looking for this blue tint to
determine the correct range was selected. Is there a better way to
verify what range is selected?

You can do the same thing from VBA. Using example above

MyRange.Select

You can step through you code in VBA by pressing F8 or settting break points
using F9. The go back to the spreadsheet a verify the corrrect cells are
selected.

Be careful when switch worksheets or workbooks when debugging code. The VBA
doesn't know you made changes. You should return back to same workbooks and
pages when you stopped before continueing.


-----------------------------------------------------------------------------------------

 

[dan dungan]

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").ClearContents
txtCount = 0
Sheets("QuotedPart").Activate
End Sub

 

[Guest]

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").ClearContents
txtCount = 0
Sheets("QuotedPart").Activate
End Sub

 

[dan dungan]

Thank you Joel.

'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

I was trying to determine what sheet the procedure was operating on
and I neglected to remove the test. Since you added the "With" clause,
the procedure is working.

Thanks for your attention and help.

Dan