Getting highest value of a coulmn

I need to get the highest value of a column (int) and then find insert a new value in the next available row...

I thought of first sorting the column with the Range.Sort and then get the last value. Any idea how to get the last value of a column??

Can anybody help me please???

Thanks

Martin

The easiest way to get the area of used cells on the Worksheet is to make use of the Worksheet.UsedRange property. The downside to this is that the "Used Range" includes cells that have any formatting, not just cells with Data. This is probably still fine for what you want to do, but if you want to test for actual Data, then the usual technique is to probe for the last row of data on your KeyID Column, which is usually the first column. The fastest way to find the last Row with data, within a given column is as follows:Shared Function WS_LastCellInColumn(ByVal xlWS As Excel.Worksheet, _
ByVal colNum As Integer) As Excel.Range
Dim numRows As Integer = xlWS.Rows.Count
Dim lastCell As Excel.Range

lastCell = DirectCast(xlWS.Cells(numRows, colNum), Excel.Range)
lastCell = lastCell.End(Excel.XlDirection.xlUp)
WS_LastCellInColumn = lastCell
End Function Then to use it you would use something like this:Shared Sub Tester()
Dim xlApp As Excel.Application
Dim xlWS As Excel.Worksheet
Dim xlRng As Excel.Range

xlWS = DirectCast(xlApp.Workbooks("Book1.xls").Worksheets("Sheet1"), _
Excel.Worksheet)
xlRng = WS_LastCellInColumn(xlWS, 1) '<-- Finds last cell in Column 1

' Note that at this point:
' (1) 'xlRng.Row' will return the number of the last Row WITH data.
' (2) 'xlRng.Row + 1' will return the first EMPTY row. :-)
End Sub I hope this makes sense!

:),
Mike

Although if you just want to find the largest value (and not the last value), you might find xlApp.WorksheetFunction.Max(range) useful. And xlApp.WorksheetFunction.Match() to then locate that value in the range.

Although if you just want to find the largest value (and not the last value), you might find xlApp.WorksheetFunction.Max(range) useful. And xlApp.WorksheetFunction.Match() to then locate that value in the range.

Im new to Excel, here is my solution. It works fine. Feel free to make any comments please!

Thanks
Martin

Here is my code
Dim xlApp As New Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlRng As Excel.Range

Dim intLastRow As Integer

xlWB = xlApp.Workbooks.Open("c:\cases.xls", , False)
xlWS = DirectCast(xlWB.Worksheets("Sheet1"), Excel.Worksheet)
xlRng = WS_LastCellInColumn(xlWS, 1)
intLastRow = xlRng.Row + 1

'Get the Max value as the data is not in order
'Using the function provided by herilane
Dim Range As Excel.Range
Range = DirectCast(xlWS.Columns(1), Excel.Range)
Dim intMaxValue As Integer
intMaxValue = xlApp.WorksheetFunction.Max(Range)
'Calculate the new value
intMaxValue = intMaxValue + 5

'Fill the new Row with data
Range = xlWS.Range("A" & intLastRow)
Range.Value = intMaxValue
Range = xlWS.Range("B" & intLastRow)
Range.Value = TextBox1.Text

'Cleanup
Range = Nothing
xlRng = Nothing
xlWB.Save()
xlWB.Close(SaveChanges:=False)
xlApp.Quit()

xlApp = Nothing
xlWB = Nothing
xlWS = Nothing

GC.Collect()

Hey Martin, looks good to me! :)

A few small comments, mostly, but nothing big.

One thing is that you are using two Range objects: 'xlRng' and 'Range'. It's up to you of course, but it might be a little unclear to you if you ever need to go back to this code again some months from now. You might want to call the first one "lastCell" and maybe the 2nd one "theColumnRng" or "maxValueRng", it's up to you.

Using the name "Range" for your object variable might also get people confused because it is the name of a Method, namely Application.Range() or Worksheet.Range(). Technically, it's fine, but others reading your code might get thrown off (I was!).

Also, be aware of the Cells() function, it can help you when dealing with cell addresses, particularly when you need Column Numbers greater than 26. For example, take the following:Range = xlWS.Range("B" & intLastRow) The above could instead be changed to:Range = DirectCast(xlWS.Cells(intLastRow, 2), Excel.Range) Ok, I have to admit that in .Net we need DirectCast() here, unfortunately, so it's a little ugly, but the point is that Cells() is indexed as .Cells(intRow, intCol) so it's an easier indexing system, particularly if your intCol values are large.

The only other comment that I might make is that you might want to turn 'Option Strict On' for your Project. This way the compiler will tell you where you need to use your DirectCast() calls. It may tell you to put it in so many places that it drives you crazy, but I think it's worth the effort it requires. One place that it will tell you to put a DirectCast() would be here:Dim intMaxValue As Integer
intMaxValue = xlApp.WorksheetFunction.Max(Range) The trouble is that the Max() function returns a Double data type and holds values that are potentially larger than the Integer variable that you are assgning it to. So you have two choices to fix this. (1) You can Cast it:Dim intMaxValue As Integer
intMaxValue = DirectCast(xlApp.WorksheetFunction.Max(Range),Integer)or (2) you can dieclare your variable 'As Double':Dim maxValue As Double
maxValue = xlApp.WorksheetFunction.Max(Range) Anyway, I hope this helps, overall your code looks very good. The cleanup at the end (the most important part!) looks perfect.

Happy New Year :),
Mike

Mike, your comments are clear and are very useful to a newbie like me. I appreciate your concern and will keep your comments in mind.

I really look up to someone that makes such an effort in every answer

Thank you so much
and a Happy New Year to you too

Martin