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" wrote:

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


On Sep 26, 6:59 pm, Joel wrote:
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.

"Don Guillett" wrote:
Post ONLY in the group where you want an answer. Otherwise, you waste a lot
of responders time.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dan dungan" wrote in message
ups.com...
Hi,

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

Private Sub cmdComplete_Click()
Worksheets("CompleteQuote").Range("A19:I19").End(x lDown).Select
DoTheExport
Clear_Unlocked2
Worksheets("CompleteQuote").Range("A19:G65536").Cl earContents
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