+ Reply to Thread
Results 1 to 15 of 15

Deleting cells and shifting them left

  1. #1
    Registered User
    Join Date
    05-25-2010
    Location
    Florida
    MS-Off Ver
    Excel 2009
    Posts
    39

    Deleting cells and shifting them left

    I have a lot of rows and very spread out information in cells. I have a bunch of cells empty, then a full one, then a bunch of them empty and it is just a pain to read it. If I could just delete all those blank cells and shift left it would make my life a lot easier
    (SOLVED)
    Last edited by knitterkuba; 12-27-2010 at 05:12 PM.

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Deleting cells and shifting them left

    knitterkuba,

    Please attach your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

    This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

    To attach your workbook (or sample), with before and after worksheets, click on the New Post button, then scroll down and click on the Manage Attachments button.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Registered User
    Join Date
    05-25-2010
    Location
    Florida
    MS-Off Ver
    Excel 2009
    Posts
    39

    Re: Deleting cells and shifting them left

    I probably should have done it from the start. I wasn't able to attach the entire thing so I just cut out a piece. I hope this helps. I tried selecting everything, pressing F5, clicking on blank cells. The problem there was that it would only select the top row.
    If you are curious, then this is a part of a project that will check whether a mistake in typing a stock's ticker has any influence on the market.
    Attached Files Attached Files

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Deleting cells and shifting them left

    Try this:
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    05-25-2010
    Location
    Florida
    MS-Off Ver
    Excel 2009
    Posts
    39

    Re: Deleting cells and shifting them left

    Is this a macro? I have no experience with macros. I tried using one a long time ago but I couldn't get it to start. Could you just quickly explain how to insert it and start it?

  6. #6
    Registered User
    Join Date
    05-25-2010
    Location
    Florida
    MS-Off Ver
    Excel 2009
    Posts
    39

    Re: Deleting cells and shifting them left

    I think I got it to work because it did the job in the sample.xlsx but when I tried to apply it to the other one it just deleted all the cells and I was left with a blank sheet. Good thing I didn't save it afterward :D

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Deleting cells and shifting them left

    Good thing.

    So the actual workbook isn't like the sample?

  8. #8
    Registered User
    Join Date
    05-25-2010
    Location
    Florida
    MS-Off Ver
    Excel 2009
    Posts
    39

    Re: Deleting cells and shifting them left

    It is like the sample, "sample" is an actual sample of the first few rows from the actual thing. I didn't attach the whole thing because it's kind of big. Do you know why that would happen? If not then I think I could reduce it in size by a lot if I just take out all the formulas and other sheets. The difference between sample and the actual worksheet is that the actual one has about 27,000 rows and 41 columns. When I run it there, after a minute I was left with a blank worksheet.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Deleting cells and shifting them left

    Ah, now I understand. SpecialCells has a size restriction, and a non-beneficial behavior (which you discovered) when you exceed it.

    I'll fix it tomorrow if no one else does in the meanwhile.

  10. #10
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Deleting cells and shifting them left

    see attachment, run macro "test"
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Deleting cells and shifting them left

    knitterkuba,

    With the raw data by watersev, duplicated to 10,000 rows, the following code (working in chunks of 2000 rows from column A thru AR) took 1 minute and 18 seconds to run on my Lenovo T61, without error.


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


    Please Login or Register  to view this content.

    Then run the RemoveBlanksShiftLeft macro.
    Last edited by stanleydgromjr; 12-27-2010 at 12:57 PM.

  12. #12
    Valued Forum Contributor jwright650's Avatar
    Join Date
    12-10-2010
    Location
    Va, USA
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    606

    Re: Deleting cells and shifting them left

    I added a clickable textbox to what watersev created for you. Click the "Click me" textbox and see if that does what you want.

    EDIT:

    whoops, I was too slow replying...and also didn't test it with the additional rows added like in the post above.

    Anyway, after you get your macro so that it will run you can assign the macro to a clickable object if you like...I used a textbox in the sample
    Attached Files Attached Files
    Last edited by jwright650; 12-27-2010 at 01:06 PM.
    Life is like a roll of toilet paper. The closer it gets to the end, the faster it goes.
    John Wright

  13. #13
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Deleting cells and shifting them left

    Quote Originally Posted by stanleydgromjr View Post
    took 1 minute and 18 seconds
    It's too long for 10000 rows, my code handles 27000 rows in around 10 sec

  14. #14
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Deleting cells and shifting them left

    watersev,

    Nicely done.

  15. #15
    Registered User
    Join Date
    05-25-2010
    Location
    Florida
    MS-Off Ver
    Excel 2009
    Posts
    39

    Re: Deleting cells and shifting them left

    Stanley's formula worked great. Everything is nicely aligned to the left
    Thank you all very much for the help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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