VB SCRIPT HELP - Delete Data from an excel spreadsheet based on certain cell value

laurensims41

New Member
Joined
Feb 4, 2016
Messages
22
I Have to pull a report every morning that is very lengthy and has everyone in the organization's data on it. I would like to be able to delete everyone else's data that are not the 10 people that report up to me


The Spreadsheet goes from Cells A to AK

COLUMN AM has the data of the people I would like to keep the information on. Any Help would be appreciated.
Lauren Sims
Jocy Sims
Kendal Perkins
Breanne Lewis
James Sims
Laura Bedwell
Betsy Bedwell
Ryan Enochs
Chris Bowne
Shannon Erlenbush
Danielle Futrell
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
995
Office Version
  1. 2010
Platform
  1. Windows
Assuming row 1 A-AK is headers and AM1 has the exact same header as the column you look in for the names
and your people are in AM2 and down


You can extract your people to another sheet using Advanced Filter
Something along the lines of this
Code:
Sub ExtractMyPeople()


Dim src As Worksheet, dest As Worksheet
Dim lr As Long, critRng As Range


Set src = Sheets("Sheet1")
Set dest = Sheets("Sheet2")


With src
    lr = .Columns("A:AK").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set critRng = .Range("AM1", .Cells(Rows.Count, "AM").End(xlUp))
    .Range("A1:AK" & lr).AdvancedFilter xlFilterCopy, critRng, dest.Cells(1)
End With


End Sub
 

laurensims41

New Member
Joined
Feb 4, 2016
Messages
22
It didn't pull any data passed Column AM, but it did put data into another sheet. I really appreciate your help on this.

Your above Assumptions are correct.

The Column AM is titled Buyer

I am not seeing where I would designate the names of the individuals I am trying to pull out of the report?
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
995
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

It didn't pull any data passed Column AM
Should it ?
From post 1
The Spreadsheet goes from Cells A to AK
made me wonder about column AM :confused:
I can't see your spreadsheet, can only guess at what you're working with from how you describe it.
Perhaps you want to elaborate a little more ?
 

laurensims41

New Member
Joined
Feb 4, 2016
Messages
22
I am sorry I really mis-typed the original ask.

The Spreadsheet is from A to AZ.
Header's in ROW 1.



I would like all data associated with the following names moved to a separate sheet or everyone deleted: These Names are in Column AN w/ a Header BUYER;
Lauren Sims
Jocy Sims
Kendal Perkins
Breanne Lewis
James Sims
Laura Bedwell
Betsy Bedwell
Ryan Enochs
Chris Bowne
Shannon Erlenbush
Danielle Futrell
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,316
Office Version
  1. 365
Platform
  1. Windows
Do you want to keep those names, or delete them?
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
995
Office Version
  1. 2010
Platform
  1. Windows
How about this ?
You add a new sheet and name it People
In A1 put the same header as your AN (Buyer) column has.
In A2 and down, list the people whose data you want to extract to another sheet.
This range on the People sheet, (A1:A12) according to the list of 11 names you've supplied, will be used as the criteria range to advance filter the src sheet.

Code:
Sub ExtractMyPeople()

    Dim src As Worksheet, dest As Worksheet
    Dim lr As Long, critRng As Range

Set src = Sheets("Sheet1")      'change sheet name if necessary
Set dest = Sheets("Sheet2")     'change sheet name if necessary
Set critRng = Sheets("People").Range("A1", Sheets("People").Range("A" & Rows.Count).End(xlUp))

With src
    lr = .Columns("A:AZ").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    .Range("A1:AZ" & lr).AdvancedFilter xlFilterCopy, critRng, dest.Cells(1)
End With

End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,994
Messages
5,526,125
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top