The problem with xldown is you can get multple different reuslts that you
aren't expecting. xldown can be simulted in the worksheet by using the keys
Shift-Cntl + Down Arrow

Try an experiment

1) Have No data in columns U. Select cell U2. then press the keys
Shift-Cntl + Down Arrow. The entire columns is highlighted
2) Repeat with data in only U2
3) Repeat with data in U2 + U3
4) Repeat with data in U2 + U3 + U4

You sort range is starting at row 3. Sort when you only have data in row 2
(test number 2 above) You will get an error because row 3 is empty.

The best method when sorting is to have a header row and use XLUP instead of
XLDOWN. Then make sure you have data to sort meaning U2 and U3 contain data.



"Risky Dave" wrote:

Hi,

I am trying to pass the number of rows in a data range to an integer
variable but I don't know how many rows of data there are.

I have:
Dim iRowCount as Integer

iRowCount = Cells(2, "U").End(xlDown).Row ' get number of rows to be sorted
Range("T3:Y" & iRowCount).Select
' this will subsequently sorted

Which doesn't work (returns the error Run Time error 6 Overflow), though
the line:
Msgbox(Cells(2, "U").End(xlDown).Row)

returns the correct value in the message box.

I assume that I need to do some sort of conversion to force the value
returned by Cells(2, "U").End(xlDown).Row into an integer but don't know the
syntax.

Any help would be appreciated.

TIA

Dave