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

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

Joel,

Thanks for the quick response.

I'll re-write using XLUP as you suggest, but interestingly if I make the
RowCount a Long instead of an integer it works! Makes no sense to me as there
are only 6 rows in my test data set

Dave

"Joel" wrote:

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

you can use something like this:

Dim rng As Range
Set rng = Range("U2:" & Range("U2").End(xlDown).Address)
Debug.Print rng.Address

then just use rng to do your sort
--


Gary Keramidas
Excel 2003


"Risky Dave" wrote in message
...
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

You must have an older version of excel becasue I've seen postings saying
that VBA really sotres integers and longs. You must be getting more than 256
rows if a long fixes the problem.

"Risky Dave" wrote:

Joel,

Thanks for the quick response.

I'll re-write using XLUP as you suggest, but interestingly if I make the
RowCount a Long instead of an integer it works! Makes no sense to me as there
are only 6 rows in my test data set

Dave

"Joel" wrote:

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