+ Reply to Thread
Results 1 to 5 of 5

VBA to Lookup all Rows based on Column Header

  1. 11-14-2018, 03:14 PM #1
    matt85webb
    matt85webb is offline
    Registered User
    Join Date
    10-06-2014
    Location
    Cincinnati
    MS-Off Ver
    2013
    Posts
    56

    VBA to Lookup all Rows based on Column Header

    Hi all-

    I'm having trouble with finding ways I can return all values in a large dataset based on only column headers. I'm trying to build a template for someone and they currently have to delete all rows irrelevant. I planned to use a separate sheet that only had the 4 columns they needed. The headers are not always in the same spot so I was trying to see if you could define a range such as find "Header 3" in Range A1:AA1 and then it will return all values beneath it until it hits the last row.

    Header 1 Header 2 Don't Need Don't Need Header 3 Don't Need
    A B X X C X
    D E X X F X


    Any help would be greatly appreciated!
    Reply With QuoteRegister To Reply
  2. 11-14-2018, 03:43 PM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,317

    Re: VBA to Lookup all Rows based on Column Header

    Put the required headings in row 1 of the new sheet, A1, B1, and so on

    Then, in A2, use
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    and copy across and down
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 11-14-2018, 03:49 PM #3
    JLGWhiz
    JLGWhiz is offline
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: VBA to Lookup all Rows based on Column Header

    Say your header is "Date" and all headers are in Row 1 on sheet 1.

    Please Login or Register  to view this content.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!
    Reply With QuoteRegister To Reply
  4. 11-14-2018, 04:35 PM #4
    matt85webb
    matt85webb is offline
    Registered User
    Join Date
    10-06-2014
    Location
    Cincinnati
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Lookup all Rows based on Column Header

    This one doesn't appear to allow for copying down. Thanks for your help!
    Reply With QuoteRegister To Reply
  5. 11-14-2018, 07:23 PM #5
    JLGWhiz
    JLGWhiz is offline
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: VBA to Lookup all Rows based on Column Header

    Quote Originally Posted by matt85webb View Post
    This one doesn't appear to allow for copying down. Thanks for your help!
    If you are referrig to Post #3, it is not intended to be copied down. It shows how to find a column of data using the header title If you want to copy the data to another sheet you would need more code. To find more than one header you can either use an array with the header names, or if the names are listed on the destination sheet, you can use thoae to loop through and find them on the source sheet and copy the columns to the destination sheet. I didn't do the whold code because I thought you wanted to do it.

    this is what a coomplete code would look like

    Please Login or Register  to view this content.
    Last edited by JLGWhiz; 11-14-2018 at 07:43 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Delete Rows based on Column Header
    By eliasc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-17-2016, 10:19 AM
  2. [SOLVED] Summing Rows based on Column Header
    By HCLax in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-01-2015, 09:56 AM
  3. Using Lookup? Index match? to populate cells based on column header
    By NoExcelSkills17 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-15-2015, 03:50 PM
  4. [SOLVED] Create Dynamic Column Lookup Reference based on Table Header
    By jeversf in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-22-2013, 12:49 PM
  5. Lookup Column Header based on cell value
    By kingcal in forum Excel General
    Replies: 4
    Last Post: 08-13-2009, 03:56 AM
  6. Lookup a column header based on first column with value greater than zero
    By panamajack in forum Excel General
    Replies: 1
    Last Post: 01-30-2009, 07:40 PM
  7. Lookup a column header based on first column with value >0
    By jasonanthony in forum Excel General
    Replies: 1
    Last Post: 01-28-2009, 08:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1