Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / November 2007

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
ucanalways@gmail.com - 30 Nov 2007 20:15 GMT
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 - 30 Nov 2007 20:33 GMT
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

Signature

HTH...

Jim Thomlinson

> I have the following code to find the max value in range E of a
> worksheet.
[quoted text clipped - 26 lines]
> any help. This problem has already consumed my 4 hours this morning..
> Thanks
ucanalways@gmail.com - 30 Nov 2007 21:19 GMT
On Nov 30, 12:33 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com> wrote:
> 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
[quoted text clipped - 52 lines]
>
> - 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 - 30 Nov 2007 23:05 GMT
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

Signature

HTH...

Jim Thomlinson

> On Nov 30, 12:33 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
> This-.com> wrote:
[quoted text clipped - 74 lines]
>
> Please let me know.. Thanks
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.