Setting Range by calculation

[Guest]

I would like to create a sort macro for a range of cells that can vary in
length. The length is dependent upon a list the user pastes into another
tab. In my working tab I have references to the pasted tab for up to 200
entries. The user might paste anywhere from 6 to nearly 200 lines into the
paste tab. Then those entries show up in my work tab. The entries are
reorganized into 8 columns, so I know the start of the range is always A8 and
the end of the range is always H something.

2 questions.
1. What is the easiest formula to check for the end of the range I want to
sort? I know the value in cell A of the row following the last row will be
Zero and no cells above it will have this value.

2. How do I insert that formula into the range statement in VBA so that the
sort will operate only down to the row with the last value in it?

Thanks in advance for any assistance you can provide.
ed

 

[Guest]

(1) A simple way of finding the last row in a range is:

Function GetLastRow()
Dim MaxRows As Long

With Application

'Count number of rows (version 2003: 65536)
MaxRows = .Range("A:A").Rows.Count
'Find the last row of the data range
GetLastRow = .Range("A1:A" & MaxRows)(MaxRows, 1) _
.End(xlUp).Row 'Start at bottom and go up

End With

End Function

(2) Is it necessary to limit the sort? You might consider the following
where j is the last row.

'Set range object equal to worksheet range
Set rng = .Worksheets("Sheet1").RANGE(Cells(1, 1), Cells(i, j))

'Sort range
rng.Sort Key1:=rng, Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[Guest]

Sorry Chad, I'm confused by your response. I'm not tryin to find the last
row in a range. If I know the range I think I would know the last row. I'm
trying to find the first row in a range that has a value of zero. Unless I
misunderstand this is very different.
As far the sort, I tried your code, but was not sure how to set "i" so I set
rng like this:
Set rng = Range("A11:H56")
Unless that change is critical, I got what I thought I would, and what I get
with a standard sort. The zero's all go to the top of the sort. Definitely
not what I'm looking for.
Thanks for trying.
ed

 

[Guest]

I guess I didn't fully understand the situation. If it is a zero you are
looking to find and you have the range reference, have you considered cycling
through the range?

Function LastRow(IP_Rng As Range)
Dim cel As Variant

For Each cel In IP_Rng
If cel = 0 Then
'Return the address of cell containing zero
'LastRow = cel.AddressLocal
'Or return the row of the cell containing zero
'LastRow = Split(StrReverse(cel.AddressLocal), "$")(0)
Exit Function
End If
Next cel

End Function