Max/Min for variable length columns

I need to find the max value in a column of unknown length, (start point at
about row 9). Then place that value in same column in next (blank) row.

Then do the same for MIN (but now it is column length -1)

This should be easy, but its been beating me all day.
Thanks

Tim,

I assume that you are able to select the first empty cell. Enter this
formula

=MAX(INDIRECT("A9:A"&ROW()-1))

and then enter

=MIN(INDIRECT("A9:A"&ROW()-1))

in the next row. The column length is irrelevant in this case as the new
number is the MAX so it will not affect the MIN formula.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Tim Rush" <Tim Rush@discussions.microsoft.com> wrote in message
news:C151BD67-B357-423C-B8C2-AF7F2A33F4DE@microsoft.com...
> I need to find the max value in a column of unknown length, (start point
at
> about row 9). Then place that value in same column in next (blank) row.
>
> Then do the same for MIN (but now it is column length -1)
>
> This should be easy, but its been beating me all day.
> Thanks
>

Sub maxMinValues()

Dim i As Long, bottomRow As Long
Dim maxValue As Double, minValue As Double

bottomRow = Cells(65536, Selection.Column).End(xlUp).Row

maxValue = Cells(9, Selection.Column).Value
For i = 9 To bottomRow
If Cells(i, Selection.Column).Value > maxValue Then
maxValue = Cells(i, Selection.Column).Value
End If
Next i
Cells(i, Selection.Column).Value = maxValue
Cells(i, Selection.Column - 1).Value = "Maximum Value"

minValue = Cells(9, Selection.Column).Value
For i = 9 To bottomRow
If Cells(i, Selection.Column).Value < minValue Then
minValue = Cells(i, Selection.Column).Value
End If
Next i
Cells(i + 1, 4).Value = minValue
Cells(i + 1, 3).Value = "Minimum Value"

End Sub

Select a cell in the relevant column and then run.

--
Gordon Rainsford

London UK


"Tim Rush" <Tim Rush@discussions.microsoft.com> wrote:

> I need to find the max value in a column of unknown length, (start point at
> about row 9). Then place that value in same column in next (blank) row.
>
> Then do the same for MIN (but now it is column length -1)
>
> This should be easy, but its been beating me all day.
> Thanks

Ok, tried that, get a 'Compile error' (Did I mention I was doing this in VBA?
and Office 2002?) Its not recognizing 'Row'. I entered my line as:

High = Max(INDIRECT("B9:B" & Row() - 1))
Tim


"Bob Phillips" wrote:

> Tim,
>
> I assume that you are able to select the first empty cell. Enter this
> formula
>
> =MAX(INDIRECT("A9:A"&ROW()-1))
>
> and then enter
>
> =MIN(INDIRECT("A9:A"&ROW()-1))
>
> in the next row. The column length is irrelevant in this case as the new
> number is the MAX so it will not affect the MIN formula.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Tim Rush" <Tim Rush@discussions.microsoft.com> wrote in message
> news:C151BD67-B357-423C-B8C2-AF7F2A33F4DE@microsoft.com...
> > I need to find the max value in a column of unknown length, (start point
> at
> > about row 9). Then place that value in same column in next (blank) row.
> >
> > Then do the same for MIN (but now it is column length -1)
> >
> > This should be easy, but its been beating me all day.
> > Thanks
> >
>
>
>

Gordon Rainsford <grbridgeREMOVETHIS@btinternet.com> wrote:

> Cells(i + 1, 4).Value = minValue
> Cells(i + 1, 3).Value = "Minimum Value"

These final two lines should read:

Cells(i + 1, Selection.Column).Value = minValue
Cells(i + 1, Selection.Column - 1).Value = "Minimum Value"


--
Gordon Rainsford

London UK

That did it... It make sense too (go figure), however, I did nodify the last
2 lines to
Cells(i + 1, Selection.Column).Value = minValue which keeps it in the same
column.

"Gordon Rainsford" wrote:

> Sub maxMinValues()
>
> Dim i As Long, bottomRow As Long
> Dim maxValue As Double, minValue As Double
>
> bottomRow = Cells(65536, Selection.Column).End(xlUp).Row
>
> maxValue = Cells(9, Selection.Column).Value
> For i = 9 To bottomRow
> If Cells(i, Selection.Column).Value > maxValue Then
> maxValue = Cells(i, Selection.Column).Value
> End If
> Next i
> Cells(i, Selection.Column).Value = maxValue
> Cells(i, Selection.Column - 1).Value = "Maximum Value"
>
> minValue = Cells(9, Selection.Column).Value
> For i = 9 To bottomRow
> If Cells(i, Selection.Column).Value < minValue Then
> minValue = Cells(i, Selection.Column).Value
> End If
> Next i
> Cells(i + 1, 4).Value = minValue
> Cells(i + 1, 3).Value = "Minimum Value"
>
> End Sub
>
> Select a cell in the relevant column and then run.
>
> --
> Gordon Rainsford
>
> London UK
>
>
> "Tim Rush" <Tim Rush@discussions.microsoft.com> wrote:
>
> > I need to find the max value in a column of unknown length, (start point at
> > about row 9). Then place that value in same column in next (blank) row.
> >
> > Then do the same for MIN (but now it is column length -1)
> >
> > This should be easy, but its been beating me all day.
> > Thanks
>
>
>

Got it, Thanks...

"Gordon Rainsford" wrote:

> Gordon Rainsford <grbridgeREMOVETHIS@btinternet.com> wrote:
>
> > Cells(i + 1, 4).Value = minValue
> > Cells(i + 1, 3).Value = "Minimum Value"
>
> These final two lines should read:
>
> Cells(i + 1, Selection.Column).Value = minValue
> Cells(i + 1, Selection.Column - 1).Value = "Minimum Value"
>
>
> --
> Gordon Rainsford
>
> London UK
>