Iterate Range

[AlexanderBB]

Just wondering if there's a preferred way to extract the High and low row numbers. My range address is $C$54:$C$400.
Do I use a For-Next Loop ? If yes, is 54 and 400 available from the range ? I could Split on "$" and remove the ':' but doesn't seem very elegant.
I looked at For each item In range ( cell1, cell2) but that would need still need the range values.
There's also a need sometimes to go 400 to 54.

 

[Micron]

Not clear to me what numbers you want. Row numbers that contain the high and low values, or just the values? I guess you ruled out just using sheet functions like Min and Max? You can get the row number for a max value in a method like this

Dim var As Variant
Dim rng As Range

Set rng = Sheets("Sheet 3").Range("I1:I12")
var = Application.WorksheetFunction.Max(rng)
Set rng = rng.Find(what:=var)
If Not rng Is Nothing Then MsgBox rng.Row

If there can be multiple max values then what?

 

[Scott Huish]

Where does this range address $C$54:$C$400 come from? What are you trying to accomplish?

 

[AlexanderBB]

From the range address (example $C$54:$C$400) it's getting the column values to use.
Perhaps Split is a good as anything?
But that was a bad example as the column didn't change, but if they did... and it was the same row, e.g. $Y$23:$AD$23
Did not know about Min/Max or you could use "Sheets" instead of "Worksheets". Thanks

 

[AlexanderBB]

$C$54:$C$400 was an example. Means nothing.
The 54 and 400 from it are wanted, or in msg4 columns Y-AD.

 

[Scott Huish]

But why are you splitting it, where does any range come from (where are they specified at)?? If it's in the source code, why not just specify it?

Like here, forward and backward.

Sub test()
Dim c As Long
For c = 54 To 400
    Range("G" & c) = c
Next
End Sub

Sub test2()
Dim c As Long
For c = 400 To 54 Step -1
    Range("G" & c) = c
Next
End Sub

 

[Micron]

So I'm guessing that the range is not fixed since "it means nothing" (even though it probably does) and what is wanted is either the min and max row numbers if the range is one column, or the min and max of the columns if the range is in one row. You would need to know the range at the start.

In that case you can get the number of rows in the range. If = 1, get the Column alpha, or if >1 get the row numbers. How to get the column alpha probably depends on whether or not to include the $. If that is the goal then I see where the idea of Split is coming from, although it's probably not necessary. However, I think validation would need to ensure the range is not multiple columns as well as multiple rows.

 

[AlexanderBB]

Thanks. I've re=worked it so defining a range isn't needed. There will always be just one row.
And now not needed, Split still seems an ok way.

 

[kvsrinivasamurthy]

Formula;
Min Row

=MIN(ROW($C$54:$C$400))

Maximum row . ARRAY formula

=MAX(ROW($C$54:$C$400))

Macro Solution.

Sub RoNum()
Dim Minro&, Maxro&
Minro = Range("$C$54:$C$400").Row
Maxro = Minro + Range("$C$54:$C$400").Rows.Count - 1
End Sub

 

[Micron]

I suspect getting column letters won't be as simple as getting row numbers. This may be clunky but seems to do the trick (or you've already solved it?).

Dim rng As Range
Dim i As Integer
Dim strCols As String, aryCols() As String, strOut As String

Set rng = Sheets("Sheet 3").Range("$Y$23:$AD$23")
If rng.rows.Count = 1 Then
    strCols = Range(rng.Address).Address(False, False)
    aryCols = Split(strCols, ":")
    For i = 1 To Len(aryCols(0))
        If Not IsNumeric(Mid(aryCols(0), i)) Then strOut = strOut & Mid(aryCols(0), i, 1)
    Next
    strOut = strOut & "; "
    For i = 1 To Len(aryCols(1))
        If Not IsNumeric(Mid(aryCols(1), i)) Then strOut = strOut & Mid(aryCols(1), i, 1)
    Next
    MsgBox strOut
End If

Wish I had learned RegEx.