+ Reply to Thread
Results 1 to 11 of 11

Delete empty columns in Excel

  1. #1
    Registered User
    Join Date
    03-22-2018
    Location
    Taunton, England
    MS-Off Ver
    10
    Posts
    35

    Delete empty columns in Excel

    Hi there

    I have a macro that manages to delete all empty columns in a worksheet and columns that have partial data in them. How can I amend the macro so that it specifically deletes totally empty columns and not partially empty columns?
    Please Login or Register  to view this content.
    Many thanks in advance

    BigTP
    Last edited by BigTP; 05-10-2018 at 10:39 AM.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Delete empty columns in Excel

    Deleted as BigTP took care of issue. Thank you.
    Not being knowledgeable in VBA cannot offer help.
    Good luck!
    Last edited by Sam Capricci; 05-10-2018 at 10:44 AM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    03-22-2018
    Location
    Taunton, England
    MS-Off Ver
    10
    Posts
    35

    Re: Delete empty columns in Excel

    There you go. Not posted code befor

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Delete empty columns in Excel

    You have indicated that you are testing for an empty column. Is this a fact? Because it appears that your code is only testing the last cell in a column to determine if it is empty. Please clarify exactly what you wish to happen so that we can either correct the existing code or provide you with new code.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Registered User
    Join Date
    03-22-2018
    Location
    Taunton, England
    MS-Off Ver
    10
    Posts
    35

    Re: Delete empty columns in Excel

    Hi there

    I am indeed looking for empty columns. The code I have I did not realise was testing the last cell in a column to determine if it is empty. This is the closest code I could find to come anywhere close to the result I require. So A and B columns will have data in them but this data refers to data further along the rows in differing columns - the only constant will be a date as the data has been date filtered before import.

    Tony

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Delete empty columns in Excel

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-22-2018
    Location
    Taunton, England
    MS-Off Ver
    10
    Posts
    35

    Re: Delete empty columns in Excel

    Hi there

    Unfortunately this does not do anything when run. I have attached a copy of the worksheet so you can see the problem.

    Tony
    Attached Files Attached Files

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Delete empty columns in Excel

    I created a test page and it worked for me. I am guessing you have some non-printable characters in your data scheme that is preventing the macro from working as it sees characters there. Is this data being entered by you or are you gleaning it from some other source. If from some other source then I suspect that you have non-printable characters that need to be removed.

    Here is an explanation of that https://www.lifewire.com/remove-non-...acters-3123798

    Then look here for how to clean these problem cells. Once you have cleaned the file, the code I provided should work. As I said, it worked for me in a sample file I created for testing.

    clean up code: http://eforexcel.com/wp/vba-macro-to...le-characters/

    EDIT:
    I ran the cleanup file on your sample file and then ran my code. It worked perfectly. Suspicions validated.
    Last edited by alansidman; 05-11-2018 at 05:11 PM.

  9. #9
    Registered User
    Join Date
    03-22-2018
    Location
    Taunton, England
    MS-Off Ver
    10
    Posts
    35

    Re: Delete empty columns in Excel

    Hi Alan

    I am copying and then pasting the values from another worksheet in the same workbook. The blank cells in the original sheet I copy from are actually not blank but have formulae in them. So that may be the root cause. I thought that copying and pasting just the values would alleviate the non-printable characters but obviously not! I did not want to run a macro to remove blank cells with "hidden" fomulae in (i.e. the formula has no value (not even 0).

    Tony

  10. #10
    Registered User
    Join Date
    03-22-2018
    Location
    Taunton, England
    MS-Off Ver
    10
    Posts
    35

    Re: Delete empty columns in Excel

    Hi Alan

    I have now combined the two macros and have a working macro that a) cleans up the data an b) removes the blank columns. Many thanks for your help.

    Tony

  11. #11
    Registered User
    Join Date
    03-22-2018
    Location
    Taunton, England
    MS-Off Ver
    10
    Posts
    35

    Re: Delete empty columns in Excel

    Hi Alan

    The macro is working very well but (always a but!) I need the columns (and cells) to retain their original format. As it stands, the format changes basically because although the cells are formatted to General when the macro runs all the leading zeros disappear (I believe this is the way Excel General format deals with numbers?). So I am not sure if it is a formatting issue, all formatting goes out of the window when the macro runs. If I format the worksheet as TEXT the macro, of course does not run.

    Any ideas?

    Tony

+ 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: 5
    Last Post: 10-06-2017, 03:59 PM
  2. VBA To Delete All Empty Columns
    By ajocius in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-31-2017, 02:07 PM
  3. How to delete empty columns
    By Smasen91 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-03-2016, 03:02 AM
  4. [SOLVED] Really Delete Blank Empty Columns
    By WmBarth in forum Excel General
    Replies: 2
    Last Post: 08-26-2015, 01:37 PM
  5. Delete Empty Columns
    By ajocius in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-10-2014, 10:05 AM
  6. Delete Empty Rows and empty columns from the word table
    By mvneema in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2014, 10:51 AM
  7. [SOLVED] Delete rows when two columns are empty
    By Musoguy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-29-2013, 09:00 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