+ Reply to Thread
Results 1 to 6 of 6

A VBA Function to sort tables based upon a column header

  1. #1
    Registered User
    Join Date
    12-11-2016
    Location
    Barton Upon Humber, England
    MS-Off Ver
    Office 365 Desktop
    Posts
    8

    Thumbs down A VBA Function to sort tables based upon a column header

    I am still new to Excel VBA and albeit I can get most things to work I am sure there is a better way to do it.
    In this example I have created a function to sort my workbook sheets based upon a table column header.
    What I really wanted to do was create a function where I could pass the sheet name, table name and column header
    into the function, but I just could not get the range correct for the sort key, so I ended up creating a function
    where I just passed the sheet name down and used an if statement for all my sheets in the workbook.
    The problem with this is I can only ever sort the using a set column in my sheets and if I add another table, I will have to remember
    to change the function.

    Can anyone help me to reach my goal and get rid of the if statements?

    Please Login or Register  to view this content.
    Thanks

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: A VBA Function to sort tables based upon a column header

    Hi, welcome.
    The idea of a function is great.
    If (and that's what my idea is based upon) you only have one table per worksheet you could simplify the macro:

    Please Login or Register  to view this content.
    There might be some syntax errors because i'm just typing this on my phone but this is the idea
    Hope it helps you a littel.

    You just pass the sheets'name and the column number in the table you want to sort
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,534

    Re: A VBA Function to sort tables based upon a column header

    A minor point; a Function returns a value to the caller. This is just another sub procedure with arguments.

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 11-22-2020 at 10:23 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: A VBA Function to sort tables based upon a column header

    you're going to have to use an array, more than likely, in the arg of the function, to get what you want. everything else should be smooth sailing. but then again, your LO names also change. so, either you'll have to loop your sheets to grab those names, or put them into an array as well. it really depends on how you have this set up. this would be a starting point for you:
    Please Login or Register  to view this content.
    that's only a guess by me. most things can be concatenated in like this, although there are exceptions to the rule.

  5. #5
    Registered User
    Join Date
    12-11-2016
    Location
    Barton Upon Humber, England
    MS-Off Ver
    Office 365 Desktop
    Posts
    8

    Thumbs up Re: A VBA Function to sort tables based upon a column header

    Thanks, AlphaFrog just what I wanted. Worked first time.

  6. #6
    Registered User
    Join Date
    12-11-2016
    Location
    Barton Upon Humber, England
    MS-Off Ver
    Office 365 Desktop
    Posts
    8

    Re: A VBA Function to sort tables based upon a column header

    Thanks for your help, but I have gone with AlphaFrog's suggestion.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 04-04-2017, 06:59 PM
  2. Sort Select Range: Either Find Column Header or Last Column:Last Row
    By mbryson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-02-2014, 08:03 AM
  3. [SOLVED] Rearrange(sort) Columns based on number in Column header string.
    By hifliers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-04-2014, 01:36 AM
  4. [SOLVED] Sort multiple columns based on column header
    By Langer101 in forum Excel Programming / VBA / Macros
    Replies: 39
    Last Post: 01-09-2013, 09:55 AM
  5. [SOLVED] Sort by column E, and create sheets named by that column, add header and data
    By salaso69 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-24-2012, 12:42 AM
  6. Keeping two tables equal based on header column
    By cyclops755 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2011, 08:41 AM
  7. Replies: 3
    Last Post: 05-17-2009, 04:31 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1