Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Help with excel 2010

Posted on 2011-09-05
Medium Priority
278 Views
Hello

Can someone please assist me in writing a macro for the attached?

Basically all I need is for the macro to search column Q ‘client’ find all matching numbers eg ‘57337’ filter on that and then in column P ‘order’ it needs to find the lowest and highest number and copy both of those rows and past into a new sheet.

Can this be done?

Any assistance is appreciated!

test.xlsx
0
Question by:vegas86
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 39

Accepted Solution

nutsch earned 2000 total points
ID: 36486487
This code should do it. Uncomment the two lines to get the title row copied too.

Thomas
``````Sub Macro2()
Dim cl As Range, lMax As Long, lMin As Long, lLoop As Long
Dim shtOrg As Worksheet, shtDest As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set shtOrg = ActiveSheet

shtOrg.Cells(1, 1).AutoFilter
shtOrg.Cells(1, 1).CurrentRegion.AutoFilter Field:=17, Criteria1:="57337"

lMax = Application.WorksheetFunction.Subtotal(4, shtOrg.Columns(16))
lMin = Application.WorksheetFunction.Subtotal(5, shtOrg.Columns(16))

lLoop = 1

'to get title row, uncomment lines below
'shtOrg.Rows(1).Copy shtDest.Rows(1)
'lLoop = 2

For Each cl In shtOrg.Cells(1, 1).CurrentRegion.Columns(16).SpecialCells(xlCellTypeVisible)
If cl = lMax Or cl = lMin Then
cl.EntireRow.Copy shtDest.Rows(lLoop)
lLoop = lLoop + 1
End If
Next

shtOrg.Cells(1, 1).AutoFilter

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
``````
0

Author Closing Comment

ID: 36486497
Works perfectly!!

Thank you!
0

LVL 39

Expert Comment

ID: 36486521

Thomas
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month7 days, 3 hours left to enroll