Specifying a range for sorting.

  • Thread starter Thread starter DocBrown
  • Start date Start date
D

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.
 
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
 
Thanks, I'll try that.

John

Jacob Skaria said:
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
Click to expand...
 
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.
 
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
 
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
 
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
 
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
 
I like this solution. It's short and concise.

Thanks, Jim

John

Jim Cone said:
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
Click to expand...
 
Instead of your code below

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

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
 
Back
Top