VBA, extracting certain rows based on value

  • Hi guys,


    I have a data set of 30,000 rows so manual would be tideous.
    Is there any way either vba or excel funciton I can extract/select the rows and paste on the next sheet based on criteria of a row?


    Ex: if col B, row 1 has 'first name', row 28 has 'first name' and so on all the way down, how can I extract all rows that the words 'first name' in it, and for context: col C row 1 and 28 would have the name.


    The reason is that I have cell strings that stay the same but the other column corresponding changes. So when I paste it to the next sheet I have all the 'first names' with the corresponding name.


    Any input would be appreciated.

  • Re: VBA, extracting certain rows based on value


    Hello, try this out! (It assumes you have headers)


  • Re: VBA, extracting certain rows based on value


    Hello, thanks for the reply.


    1) I do not have headers.


    I get error on the

    Code
    If Sheets(NSheet).Cells(2,1) = "" Then

    subscript out of range.


    I changed my example from above, instead of looking into Col B, I am looking into A now. Does that change your code?


    2) Do I have to have sheet2 already open? Or will this create it?

  • Re: VBA, extracting certain rows based on value


    If you are looking into column A it will work. You need to have 2 sheets already created. And the sheet names are specified here:

    Code
    OSheet = "Sheet1" 'Old Sheet Name
        NSheet = "Sheet2" 'New Sheet Name



    So right now the Macro assumes your sheets are titled "Sheet1" and "Sheet2". If your names are something different then you can change the names in the above code. If it will always be the first and second sheets in the workbook then you can change it to this:

    Code
    OSheet = 1 'Old Sheet Name
        NSheet = 2 'New Sheet Name


    If you want to add a new sheet then you can use this to add a sheet to the end of a workbook:

    Code
    Sheets.Add after:=Sheets(Sheets.Count)


    Or this will add a new sheet after your first sheet:


    Code
    Sheets.Add after:=Sheets(1)
  • Re: VBA, extracting certain rows based on value


    Wow works great! thanks! how would I modify to add an additional search item(s)? so instead of it being just first name, what if I wanted to add last name?
    So results would be on sheet 2:
    Row 2: First name
    Row 3: Last name
    etc etc.

  • Re: VBA, extracting certain rows based on value


    If I may ask a couple questions,
    1) How does it know which column to look into?
    2) How can I search for additional words and paste these into the results sheet beside the first words results? Then repeat.

  • Re: VBA, extracting certain rows based on value


    I'll answer this question by question:


    1. how would I modify to add an additional search item(s)? so instead of it being just first name, what if I wanted to add last name? - I can make it so you can indicate what text you would like to search for, but I'm not sure if I understand it showing up as Firstname in one row, and Lastname in another.


    This will show up like this on the new sheet if you indicated "First Name" then ran the Macro, then indicated "Last Name" and ran the macro again:


    Firstname1
    Firstname2
    FirstnameN
    Lastname1
    Lastname2
    LastnameN

    2. How does it know which column to look into. - I have specified for the macro to always look into column1 (A):


    Code
    If InStr(LCase(Cells(i, 1)), LCase(SearchTxt)) Then


    The "1" in this section of code is what references the column. I could add an input box where you specify which column you would like to look into.


    3. How can I search for additional words and paste these into the results sheet beside the first words results? Then repeat - I would need any example of what your data looks like and how you would like it to show once the macro is ran for me to be able to create this.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!