Solved: Find maximum value in a column

[JeffT]

Hi sorry to ask this which is probably quite simple but its been a couple of years since I have done any VBA.
programming
In column A on sheet 1 there is a list of numbers 1 at the top & the maximum at the bottom of the list.

The numbers are not in consecutive rows e.g 1 is always in A:3 2 could be A:10 , 3 A:19 etc.

I just want to find the maximum number in column A so this can be used as a counter in the rest of the code which works.

I imagine its simple but I've tried lots of things but it doesn't quite work. I can do it simply in the spreadsheet e.g. =MAX(A:A) then refer to that cell but I'd rather just find it with the code.

Thanks

Jeff

[shrivallabha]

You may convert your formula to VBA:

[vba]Sub CheckMax()
Dim lLastRow As Long
lLastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & lLastRow + 1).Formula = "=Max(A1:A" & lLastRow & ")"
End Sub[/vba]

[JeffT]

Hi Shrivallabha

Changing your code slightly as below got me the figure to use in the rest of the macro.

Sub CheckMax()
Dim lLastRow As Long, Count As Integer

lLastRow = Range("A" & Rows.Count).End(xlUp).Row
Count = Range("A" & lLastRow).Value

End Sub

It all works now. Thanks as always

Jeff

[shrivallabha]

Glad that it did. However, I'd recommend following line:
[vba]Count = Range("A" & Rows.Count).End(xlUp).Value[/vba]