CODEWITHSUNDEEP
vbaexcel
dsunden
dsunden

Reputation: 23

Find cell with max value of column, copy entire row and paste in row 2

I have searched the web for an answer for this but can't translate the answers I find into exactly what I want to do.

I want to find the cell in Range("L5:L" & LastRow) with maximum value, and copy that entire row and paste it on row 2.

I know the code to get the maximum value into cell L4. But I also want to copy the row which the maximum value is in. That code is below. But how to select and copy the entire row? Seems like such a simple thing, and I can't get it to work.

So this is what I have to get the value:

Range("L4").Value = Application.WorksheetFunction.max(Range("L5:L" & LastRow))

Upvotes: 0

Views: 1953

Answers (1)

bilbo_strikes_back
bilbo_strikes_back

Reputation: 591

The following will find the max value, find the first row with the max value, and take row values from the max value row and populate row 2 with the results.

Dim lngRow As Long
Dim lngMax As Long
Dim rngTemp As Range
Dim rngCell As Range
Dim lastRow As Long

lastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set rngTemp = Range("L5:L" & lastRow)
lngMax = Application.WorksheetFunction.Max(Range("L5:L" & lastRow))

For Each rngCell In rngTemp
    If rngCell.Value = lngMax Then
        lngRow = rngCell.Row
        Exit For
    End If
Next rngCell

Rows(2).Value = Rows(lngRow).Value

Upvotes: 0

Related Questions