Specifying a range for sorting.

[DocBrown]

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.

 

[Jacob Skaria]

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

 

[DocBrown]

Thanks, I'll try that.

John

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

 

[Jim Cone]

re: "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"
<[email protected]>
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.

 

[DocBrown]

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

 

[Jacob Skaria]

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]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[DocBrown]

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

Thanks, Jim

John

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" <[email protected]>
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

 

[keiji kounoike]

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