Variation on the count row question

[Risky Dave]

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]

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]

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

 

[Gary Keramidas]

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

 

[Joel]

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.