I'm sure I'm missing something simple.... I seem to be missunderstanding how
to create the range I need. I also may be trying to be too efficient with my
use of the range so, maybe I need to consider that.

I'm trying to gather unique values from a column of data, and sort it before
I put it into another area of the WS.

I have a named range "AcctList" RefersTo: $M$30:$M$160

I have a function Last that will find the last row number that has data
within the range passed in. So I want to take the Range("AcctList") and
create a new range that contains cells from the beginning of "AcctList" to
the last row determined by my function Last.

I currently have this:


Set currRange = ActiveSheet.Range("AcctList")
AccountLastRow = Last(1, currRange)
If AccountLastRow <= 0 Then Exit Function ' no data in range.

Set currRange2 = ActiveSheet.Range(currRange.Cells(1),
currRange.Cells(AccountLastRow))

But if AccountLastRow is 31, then the range is $M30:M60.

What is the correct way to use that AccountLastRow to specifiy the range?
Once I have this range, I already have the Unique and sort functions complete.

Thanks,
John S.

The below will give you the last entry in M starting from 29
lngLastrow = ActiveSheet.Cells(29, "M").End(xlDown).Row

OR

The below will give you the row number of the first entry starting from 161
upwards
lngLastrow = ActiveSheet.Cells(161, "M").End(xlUp).Row

Adjust to suit

If this post helps click Yes
---------------
Jacob Skaria


"DocBrown" wrote:

I'm sure I'm missing something simple.... I seem to be missunderstanding how
to create the range I need. I also may be trying to be too efficient with my
use of the range so, maybe I need to consider that.

I'm trying to gather unique values from a column of data, and sort it before
I put it into another area of the WS.

I have a named range "AcctList" RefersTo: $M$30:$M$160

I have a function Last that will find the last row number that has data
within the range passed in. So I want to take the Range("AcctList") and
create a new range that contains cells from the beginning of "AcctList" to
the last row determined by my function Last.

I currently have this:


Set currRange = ActiveSheet.Range("AcctList")
AccountLastRow = Last(1, currRange)
If AccountLastRow <= 0 Then Exit Function ' no data in range.

Set currRange2 = ActiveSheet.Range(currRange.Cells(1),
currRange.Cells(AccountLastRow))

But if AccountLastRow is 31, then the range is $M30:M60.

What is the correct way to use that AccountLastRow to specifiy the range?
Once I have this range, I already have the Unique and sort functions complete.

Thanks,
John S.

Thanks, I'll try that.

John

"Jacob Skaria" wrote:

The below will give you the last entry in M starting from 29
lngLastrow = ActiveSheet.Cells(29, "M").End(xlDown).Row

OR

The below will give you the row number of the first entry starting from 161
upwards
lngLastrow = ActiveSheet.Cells(161, "M").End(xlUp).Row

Adjust to suit

If this post helps click Yes
---------------
Jacob Skaria


"DocBrown" wrote:

I'm sure I'm missing something simple.... I seem to be missunderstanding how
to create the range I need. I also may be trying to be too efficient with my
use of the range so, maybe I need to consider that.

I'm trying to gather unique values from a column of data, and sort it before
I put it into another area of the WS.

I have a named range "AcctList" RefersTo: $M$30:$M$160

I have a function Last that will find the last row number that has data
within the range passed in. So I want to take the Range("AcctList") and
create a new range that contains cells from the beginning of "AcctList" to
the last row determined by my function Last.

I currently have this:


Set currRange = ActiveSheet.Range("AcctList")
AccountLastRow = Last(1, currRange)
If AccountLastRow <= 0 Then Exit Function ' no data in range.

Set currRange2 = ActiveSheet.Range(currRange.Cells(1),
currRange.Cells(AccountLastRow))

But if AccountLastRow is 31, then the range is $M30:M60.

What is the correct way to use that AccountLastRow to specifiy the range?
Once I have this range, I already have the Unique and sort functions complete.

Thanks,
John S.

"But if AccountLastRow is 31, then the range is $M30:M60"

There are 31 rows in range(M30:M60).
That appears to be the correct answer.
Were you expecting range(M30:M31)?
--
Jim Cone
Portland, Oregon USA




"DocBrown"

wrote in message
I'm sure I'm missing something simple.... I seem to be missunderstanding how
to create the range I need. I also may be trying to be too efficient with my
use of the range so, maybe I need to consider that.

I'm trying to gather unique values from a column of data, and sort it before
I put it into another area of the WS.

I have a named range "AcctList" RefersTo: $M$30:$M$160

I have a function Last that will find the last row number that has data
within the range passed in. So I want to take the Range("AcctList") and
create a new range that contains cells from the beginning of "AcctList" to
the last row determined by my function Last.

I currently have this:


Set currRange = ActiveSheet.Range("AcctList")
AccountLastRow = Last(1, currRange)
If AccountLastRow <= 0 Then Exit Function ' no data in range.

Set currRange2 = ActiveSheet.Range(currRange.Cells(1),
currRange.Cells(AccountLastRow))

But if AccountLastRow is 31, then the range is $M30:M60.

What is the correct way to use that AccountLastRow to specifiy the range?
Once I have this range, I already have the Unique and sort functions complete.

Thanks,
John S.

Yes, Given the values I supplied, I want the range to become (M30:M31). What
is the syntax for generating that range?

In the way I have coded it, it is apparent that the 31, which is the row
number is being added to the starting rwo of 30 to create that (M30:M60)
range.

Thanks,
John

"Jim Cone" wrote:

"But if AccountLastRow is 31, then the range is $M30:M60"

There are 31 rows in range(M30:M60).
That appears to be the correct answer.
Were you expecting range(M30:M31)?
--
Jim Cone
Portland, Oregon USA




"DocBrown"

wrote in message
I'm sure I'm missing something simple.... I seem to be missunderstanding how
to create the range I need. I also may be trying to be too efficient with my
use of the range so, maybe I need to consider that.

I'm trying to gather unique values from a column of data, and sort it before
I put it into another area of the WS.

I have a named range "AcctList" RefersTo: $M$30:$M$160

I have a function Last that will find the last row number that has data
within the range passed in. So I want to take the Range("AcctList") and
create a new range that contains cells from the beginning of "AcctList" to
the last row determined by my function Last.

I currently have this:


Set currRange = ActiveSheet.Range("AcctList")
AccountLastRow = Last(1, currRange)
If AccountLastRow <= 0 Then Exit Function ' no data in range.

Set currRange2 = ActiveSheet.Range(currRange.Cells(1),
currRange.Cells(AccountLastRow))

But if AccountLastRow is 31, then the range is $M30:M60.

What is the correct way to use that AccountLastRow to specifiy the range?
Once I have this range, I already have the Unique and sort functions complete.

Thanks,
John S.

Try the below function which returns the new range address..

Sub Mac()
Dim rngTemp As Range
Set rngTemp = Range("$M$30:$M$160")
MsgBox GetRangeAddress(rngTemp)

End Sub

Function GetRangeAddress(rngTemp) As String
Dim newRange As Range
lngrow = ActiveSheet.Cells(rngTemp.Row, "A").End(xlDown).Row
Set newRange = Range(Cells(rngTemp.Row, rngTemp.Column), Cells(lngrow,
rngTemp.Column))
GetRangeAddress = newRange.Address
End Function

If this post helps click Yes
---------------
Jacob Skaria


"DocBrown" wrote:

Thanks, I'll try that.

John

"Jacob Skaria" wrote:

The below will give you the last entry in M starting from 29
lngLastrow = ActiveSheet.Cells(29, "M").End(xlDown).Row

OR

The below will give you the row number of the first entry starting from 161
upwards
lngLastrow = ActiveSheet.Cells(161, "M").End(xlUp).Row

Adjust to suit

If this post helps click Yes
---------------
Jacob Skaria


"DocBrown" wrote:

I'm sure I'm missing something simple.... I seem to be missunderstanding how
to create the range I need. I also may be trying to be too efficient with my
use of the range so, maybe I need to consider that.

I'm trying to gather unique values from a column of data, and sort it before
I put it into another area of the WS.

I have a named range "AcctList" RefersTo: $M$30:$M$160

I have a function Last that will find the last row number that has data
within the range passed in. So I want to take the Range("AcctList") and
create a new range that contains cells from the beginning of "AcctList" to
the last row determined by my function Last.

I currently have this:


Set currRange = ActiveSheet.Range("AcctList")
AccountLastRow = Last(1, currRange)
If AccountLastRow <= 0 Then Exit Function ' no data in range.

Set currRange2 = ActiveSheet.Range(currRange.Cells(1),
currRange.Cells(AccountLastRow))

But if AccountLastRow is 31, then the range is $M30:M60.

What is the correct way to use that AccountLastRow to specifiy the range?
Once I have this range, I already have the Unique and sort functions complete.

Thanks,
John S.

Revised one..The earlier one retuns wrong address if you have blank rows in
between data..

Function GetRangeAddress(rngTemp) As String
Dim newRange As Range
lngRow = rngTemp.Row + rngTemp.Rows.Count
lngRow = ActiveSheet.Cells(lngRow, rngTemp.Column).End(xlUp).Row
Set newRange = Range(Cells(rngTemp.Row, rngTemp.Column), _
Cells(lngRow, rngTemp.Column))
GetRangeAddress = newRange.Address
End Function

If this post helps click Yes
---------------
Jacob Skaria


"DocBrown" wrote:

Thanks, I'll try that.

John

"Jacob Skaria" wrote:

The below will give you the last entry in M starting from 29
lngLastrow = ActiveSheet.Cells(29, "M").End(xlDown).Row

OR

The below will give you the row number of the first entry starting from 161
upwards
lngLastrow = ActiveSheet.Cells(161, "M").End(xlUp).Row

Adjust to suit

If this post helps click Yes
---------------
Jacob Skaria


"DocBrown" wrote:

I'm sure I'm missing something simple.... I seem to be missunderstanding how
to create the range I need. I also may be trying to be too efficient with my
use of the range so, maybe I need to consider that.

I'm trying to gather unique values from a column of data, and sort it before
I put it into another area of the WS.

I have a named range "AcctList" RefersTo: $M$30:$M$160

I have a function Last that will find the last row number that has data
within the range passed in. So I want to take the Range("AcctList") and
create a new range that contains cells from the beginning of "AcctList" to
the last row determined by my function Last.

I currently have this:


Set currRange = ActiveSheet.Range("AcctList")
AccountLastRow = Last(1, currRange)
If AccountLastRow <= 0 Then Exit Function ' no data in range.

Set currRange2 = ActiveSheet.Range(currRange.Cells(1),
currRange.Cells(AccountLastRow))

But if AccountLastRow is 31, then the range is $M30:M60.

What is the correct way to use that AccountLastRow to specifiy the range?
Once I have this range, I already have the Unique and sort functions complete.

Thanks,
John S.

Sub Redo()
Dim RowsCount As Long
Dim CurrRng As Range
Dim CurrRng2 As Range

RowsCount = Range("AcctList").Cells.Count
Set CurrRng = Range("AcctList")(1)
Set CurrRng2 = Range("AcctList")(RowsCount + 1).End(xlUp)
MsgBox Range(CurrRng, CurrRng2).Address
End Sub
--
Jim Cone
Portland, Oregon USA




"DocBrown"
wrote in message
Yes, Given the values I supplied, I want the range to become (M30:M31). What
is the syntax for generating that range?

In the way I have coded it, it is apparent that the 31, which is the row
number is being added to the starting rwo of 30 to create that (M30:M60)
range.
Thanks,
John





"Jim Cone" wrote:
"But if AccountLastRow is 31, then the range is $M30:M60"

There are 31 rows in range(M30:M60).
That appears to be the correct answer.
Were you expecting range(M30:M31)?
--
Jim Cone
Portland, Oregon USA

I like this solution. It's short and concise.

Thanks, Jim

John

"Jim Cone" wrote:

Sub Redo()
Dim RowsCount As Long
Dim CurrRng As Range
Dim CurrRng2 As Range

RowsCount = Range("AcctList").Cells.Count
Set CurrRng = Range("AcctList")(1)
Set CurrRng2 = Range("AcctList")(RowsCount + 1).End(xlUp)
MsgBox Range(CurrRng, CurrRng2).Address
End Sub
--
Jim Cone
Portland, Oregon USA




"DocBrown"
wrote in message
Yes, Given the values I supplied, I want the range to become (M30:M31). What
is the syntax for generating that range?

In the way I have coded it, it is apparent that the 31, which is the row
number is being added to the starting rwo of 30 to create that (M30:M60)
range.
Thanks,
John





"Jim Cone" wrote:
"But if AccountLastRow is 31, then the range is $M30:M60"

There are 31 rows in range(M30:M60).
That appears to be the correct answer.
Were you expecting range(M30:M31)?
--
Jim Cone
Portland, Oregon USA

Instead of your code below

Set currRange2 = ActiveSheet.Range(currRange.Cells(1),
currRange.Cells(AccountLastRow))

Try this one.

Set currRange2 = currRange.Resize(AccountLastRow - currRange.Row, 1)

P.S.
Do not add ActiveSheet before currRange like

Set currRange2 = ActiveSheet.currRange.Resize(AccountLastRow -
currRange.Row, 1)

Keiji

DocBrown wrote:
I'm sure I'm missing something simple.... I seem to be missunderstanding how
to create the range I need. I also may be trying to be too efficient with my
use of the range so, maybe I need to consider that.

I'm trying to gather unique values from a column of data, and sort it before
I put it into another area of the WS.

I have a named range "AcctList" RefersTo: $M$30:$M$160

I have a function Last that will find the last row number that has data
within the range passed in. So I want to take the Range("AcctList") and
create a new range that contains cells from the beginning of "AcctList" to
the last row determined by my function Last.

I currently have this:


Set currRange = ActiveSheet.Range("AcctList")
AccountLastRow = Last(1, currRange)
If AccountLastRow <= 0 Then Exit Function ' no data in range.

Set currRange2 = ActiveSheet.Range(currRange.Cells(1),
currRange.Cells(AccountLastRow))

But if AccountLastRow is 31, then the range is $M30:M60.

What is the correct way to use that AccountLastRow to specifiy the range?
Once I have this range, I already have the Unique and sort functions complete.

Thanks,
John S.