Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Help with excel 2010

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
vegas86
Asked:
vegas86
  • 2
1 Solution
 
nutschCommented:
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
Application.DisplayAlerts = False

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))

Set shtDest = Sheets.Add
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
Application.DisplayAlerts = True

End Sub

Open in new window

0
 
vegas86Author Commented:
Works perfectly!!

Thank you!
0
 
nutschCommented:
Glad to help. Thanks for the grade.

Thomas
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now