+ Reply to Thread
Results 1 to 6 of 6

Copy column, remove blanks and alphabetize

  1. #1
    Registered User
    Join Date
    10-08-2012
    Location
    NC
    MS-Off Ver
    Excel 2013
    Posts
    39

    Copy column, remove blanks and alphabetize

    I need to create a unique list of values from a column on one page and copy that to another page. Right now I'm using this code that I found online, and it's working perfectly:

    Please Login or Register  to view this content.
    However, there are some values that I want to filter (empty spaces "", general names like "Staff", and so-on). I tried using something like:

    Please Login or Register  to view this content.
    but it gives me an error. This makes sense I suppose since the copy command seems to be a direct command instead of a loop (I'm not used to a range being designated as "F:F"... I assume that means the entire column where values are present?). I tried filtering the column with a loop, but I kept getting errors. Also, as mentioned above, I also need to alphabetize the list once it's created on the second sheet. Any help would be appreciated!

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Copy column, remove blanks and alphabetize

    It is always easier to help and test possible solutions if we could work with your actual file. Could you attach a copy? Be specific as to how you want to filter the values.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    10-08-2012
    Location
    NC
    MS-Off Ver
    Excel 2013
    Posts
    39

    Re: Copy column, remove blanks and alphabetize

    The file is a bit complex layout-wise and operationally. What is the best way to proceed?

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Copy column, remove blanks and alphabetize

    F:F can be qualified as a range but it is Column F from F1 to F1,048,576. Maybe a couple more cells than needed.
    After you have that problem sussed, read Post #2 again and act accordingly for easier/possible faster help

  5. #5
    Registered User
    Join Date
    10-08-2012
    Location
    NC
    MS-Off Ver
    Excel 2013
    Posts
    39

    Re: Copy column, remove blanks and alphabetize

    Thanks! I was actually asking how best to upload the file and direct the person to where the code is and what events trigger the code. This workbook has about 10 sheets with VBA on several, as well as two modules and code in the workbook section, as well.

    I ended up using the following code to sort each column of data created:

    Please Login or Register  to view this content.
    I'm starting to get a really good understanding of using the "cells" function so I can do loops (finally!). So... I've created lists, they're sorted, and to eliminate blanks when loading them into my combobox I use the following code:

    Please Login or Register  to view this content.
    So... I'm slowly getting it figured out. Also, I use the code:

    Please Login or Register  to view this content.
    To keep blanks from loading in my list. What would be the easiest way to add other items that I can exclude?

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Copy column, remove blanks and alphabetize

    You can add an attachment to your post by clicking the 'Go Advanced' button at the lower right, then scroll down a bit and click 'Manage Attachments" and follow the instructions.

+ 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. [SOLVED] Need formula to combine values in two columns, remove empty cells, & alphabetize.
    By Big.Moe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-22-2017, 06:33 AM
  2. [SOLVED] Formula to Alphabetize A List, Excluding Blanks
    By JYTS in forum Excel General
    Replies: 4
    Last Post: 11-03-2015, 05:57 PM
  3. Remove Duplicates in column and remove blanks
    By geliedee in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-13-2015, 07:25 AM
  4. remove blanks from column but not the row
    By MOONDRIFT47 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-05-2014, 06:30 AM
  5. [SOLVED] VBA Remove Duplicates From Column & Ignore Blanks
    By hobbiton73 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-26-2014, 04:56 AM
  6. Copy Rows - Paste and remove blanks
    By tigerdel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-24-2012, 01:28 PM
  7. Scan a Column A (numbers and blanks) and copy its contents WITHOUT BLANKS into Column B
    By bighandsam in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-12-2012, 05:16 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1