Macro to select a row that has max value and paste it in an empty row

[ucanalways]

I have the following code to find the max value in range E of a
worksheet.

Option Explicit
Sub findmax()

Dim c As Range
Dim x As Double

x = Application.WorksheetFunction.Max(Range("E:E"))
Set c = Range("E:E").Find(x, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, 0).Select
End If
End Sub

For example, if range E has values in first 50 cells and if cell E35
has the max value, present code selects cell E35. Instead of just
having E35 selection, I would to have the full row 35 to be selected.
Please let me know the code for this. This is the primary thing I am
looking for.

Secondly, the selected row (row 35 in this example) must be pasted in
a row that does not have any values. In other words, if there is data
in a row, then that row(s) must be skipped and the row35 must be
pasted in a row that does not contain any data/values.

Please let me know how to accomplish this. I would really appreciate
any help. This problem has already consumed my 4 hours this morning..
Thanks

 

[Jim Thomlinson]

You are actually very close. This will copy the entire row for you. My
question is I am a little unclear exactly where you want it pasted. On this
sheet? First Blank row? Column E is populated to the bottom?

Option Explicit
Sub findmax()

Dim c As Range
Dim x As Double

x = Application.WorksheetFunction.Max(Range("E:E"))
Set c = Range("E:E").Find(x, LookIn:=xlValues
If Not c Is Nothing Then
c.entirerow.copy _
Destination:=cells(rows.count, "E").end(xlup).offset(1,0).entirerow
End If
End Sub

 

[ucanalways]

You are actually very close. This will copy the entire row for you. My
question is I am a little unclear exactly where you want it pasted. On this
sheet? First Blank row? Column E is populated to the bottom?

Option Explicit
Sub findmax()

Dim c As Range
Dim x As Double

x = Application.WorksheetFunction.Max(Range("E:E"))
Set c = Range("E:E").Find(x, LookIn:=xlValues
If Not c Is Nothing Then
c.entirerow.copy _
Destination:=cells(rows.count, "E").end(xlup).offset(1,0).entirerow
End If
End Sub

--
HTH...

Jim Thomlinson











- Show quoted text -

Hi Jim..

Thanks! your output was the one exactly what I wanted. I just added
")" at the end of this line
Set c = Range("E:E").Find(x, LookIn:=xlValues

I would like to paste c.entirerow values in the first blank row of
Sheet1 in another workbook, How would the following line be modified?

c.entirerow.copy _
Destination:=cells(rows.count, "E").end(xlup).offset(1,0).entirerow

I will have
Windows("test.xls").Activate
Sheets("Sheet1").select
c.entirerow,copy _
Destination:= ???????????????????????

Please let me know.. Thanks

 

[Jim Thomlinson]

There is no need to select...

c.EntireRow.Copy _
Destination:=Workbooks("test.xls").Sheets("Sheet1").Cells(Rows.Count, _
"E").End(xlUp).Offset(1, 0).EntireRow