+ Reply to Thread
Results 1 to 11 of 11

Delete blank cells and move data to left

  1. #1
    Registered User
    Join Date
    08-24-2016
    Location
    glasgow
    MS-Off Ver
    2007
    Posts
    9

    Delete blank cells and move data to left

    I have scattered data in Columns AS to BS (2 to 41786 rows). I would like to delete all the blank cells and move all the data to the left (starting from AS column).
    I am not good with macros/VBA etc. Just trying to learn and have recently added 'developer' in my Excel (2007).

    I have searched for this online and though I see some questions/solutions on this, I am unable to amend it to work on my data.

    Can someone help me please?
    Thanks in advance!!!

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Delete blank cells and move data to left

    I don't know if this works in Excel 2007. Mine is inaccessible at the moment.

    If it is similar to Excel 2013 and if I understand correctly and the blank cells are true blanks ... ie not empty strings ("") or containing space(s) this has worked for me.
    1. Select the whole range in question AS1:BS41786.
    2. Press the F5 function key.
    3. Select 'Special....'
    4. Click the 'Blanks' radio button.
    5. Click OK. (This should select a non-contiguous range of blank cells.)
    6. Now right click in one of those cells.
    7. Click Delete.
    8. From the fly out menu click 'Shift cells left'
    Dave

  3. #3
    Registered User
    Join Date
    08-24-2016
    Location
    glasgow
    MS-Off Ver
    2007
    Posts
    9

    Re: Delete blank cells and move data to left

    Thanks Dave

    This works for small data, but when I use this function, a message pops up that the area is too large.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Delete blank cells and move data to left

    Hmmm.

    Is the small data large enough to make it practical to do in steps?

    If that is not satisfactory I am at a loss unless someone with VBA expertise has something.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Delete blank cells and move data to left

    Hi,

    Can you post a sample workbook showing your data. Then I can see if a VBA solution works.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  6. #6
    Registered User
    Join Date
    08-24-2016
    Location
    glasgow
    MS-Off Ver
    2007
    Posts
    9

    Re: Delete blank cells and move data to left

    For some odd reasons, I am not able to attach the file. I have just taken screen shots of what it looks like (first picture) and what I am looking for (second picture first few rows). Note I have just moved the data to columns G:AJ and there are 41786 rows of data

    1.png

    2.png

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Delete blank cells and move data to left

    Hi DRS,

    Here is the code that should do what you want. From column AS to BS. It will take a little time to do all rows but should work.
    Please Login or Register  to view this content.
    In your picture you show from column "G" to the right but in your post you ask for AS to BS.

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Delete blank cells and move data to left

    Hi DRS,

    Instead of sending me a 3.5 meg file, simply send me a file that has the first 100 (or so) rows of your worksheet and I'll attach my macro to it and send it back. I need to know which column the data starts to shift left. You wrote AS but in your picture it was column G.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  9. #9
    Registered User
    Join Date
    08-24-2016
    Location
    glasgow
    MS-Off Ver
    2007
    Posts
    9

    Re: Delete blank cells and move data to left

    Thanks
    I have now attached a file. The actual unformatted data is from G:AF and from rows 2:41786. I have attached a sample of how the data looks like. After formatting I would like the data to appear like what I have in Columns AG to AI (green) depending on the number of observations in each row (can extend beyond column AI).
    Attached Files Attached Files

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Delete blank cells and move data to left

    Hi DRS,

    I find my assumption about all those blank cells being selected was wrong. Try this one instead. I moved the answer to the left. Run the Macro and see if it does what you need.

    Delete Blank cells Shift Left.xlsm

    Code looks like this:
    Please Login or Register  to view this content.
    Sorry I didn't get it right the first time.
    Last edited by MarvinP; 04-18-2017 at 06:56 PM.

  11. #11
    Registered User
    Join Date
    08-24-2016
    Location
    glasgow
    MS-Off Ver
    2007
    Posts
    9

    Re: Delete blank cells and move data to left

    Thank you sooo much. It works wonderfully...

+ 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] Macro to move data to the left when there are blank cells.
    By daisiedawn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-23-2016, 06:15 PM
  2. [SOLVED] Function to move text data to left blank cell
    By Karnik in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-12-2016, 08:03 PM
  3. Delete cells if blank, shift left until data found
    By wishkey in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-04-2013, 08:22 PM
  4. [SOLVED] Move data row by row to left hand side of an array across blank cells
    By Fred99 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-11-2012, 06:34 PM
  5. To copy, move, and then delete old row of data left.
    By Cyberpawz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-22-2012, 04:05 AM
  6. VBA to delete cells that are blank and 0, move to left
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2011, 11:07 AM
  7. delete blank cells and shift data left
    By rhudgins in forum Excel General
    Replies: 7
    Last Post: 07-30-2010, 11: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