PDA

View Full Version : Solved: Find maximum value in a column



JeffT
02-06-2011, 03:57 AM
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
02-06-2011, 06:03 AM
You may convert your formula to 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

JeffT
02-06-2011, 06:57 AM
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
02-06-2011, 08:08 AM
Glad that it did. However, I'd recommend following line:
Count = Range("A" & Rows.Count).End(xlUp).Value