+ Reply to Thread
Results 1 to 9 of 9

VBA - Shift data in range (row) left towards first cell WITHOUT deleting cells.

  1. #1
    Forum Contributor
    Join Date
    12-06-2012
    Location
    sacramento,calif
    MS-Off Ver
    Excel 2010
    Posts
    217

    VBA - Shift data in range (row) left towards first cell WITHOUT deleting cells.

    Problem:
    A1: 1 B1: 2 C1: (empty) D1: 3 E1: 4

    After solution is applied:
    A1: 1 B1: 2 C1: 3 D1: 4
    Please click on * Add Reputation if my solution was useful, thanks

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: VBA - Shift data in range (row) left towards first cell WITHOUT deleting cells.

    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: VBA - Shift data in range (row) left towards first cell WITHOUT deleting cells.

    What about
    Please Login or Register  to view this content.
    - Battle without fear gives no glory - Just try

  4. #4
    Forum Contributor
    Join Date
    12-06-2012
    Location
    sacramento,calif
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: VBA - Shift data in range (row) left towards first cell WITHOUT deleting cells.

    This code worked on the first few attempts but then failed when for example A1 to FF1 are not empty but every cell to the right of this range is empty:
    Set rBlank = Rows("1:1").SpecialCells(xlCellTypeBlanks)
    Run time error 1004: No cells were found

  5. #5
    Forum Contributor
    Join Date
    12-06-2012
    Location
    sacramento,calif
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: VBA - Shift data in range (row) left towards first cell WITHOUT deleting cells.

    Quote Originally Posted by PCI View Post
    What about
    Please Login or Register  to view this content.
    Sorry, can't delete cells as per description.

  6. #6
    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: VBA - Shift data in range (row) left towards first cell WITHOUT deleting cells.

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

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: VBA - Shift data in range (row) left towards first cell WITHOUT deleting cells.

    See another one
    Please Login or Register  to view this content.
    Last edited by PCI; 03-13-2018 at 06:44 PM.

  8. #8
    Forum Contributor
    Join Date
    12-06-2012
    Location
    sacramento,calif
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: VBA - Shift data in range (row) left towards first cell WITHOUT deleting cells.

    Quote Originally Posted by PCI View Post
    See another one
    Please Login or Register  to view this content.
    Very Good.

  9. #9
    Forum Contributor
    Join Date
    12-06-2012
    Location
    sacramento,calif
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: VBA - Shift data in range (row) left towards first cell WITHOUT deleting cells.

    Quote Originally Posted by shg View Post
    Please Login or Register  to view this content.
    Very clever code. Thanks.

+ 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. Limit Shift Cells to Left to a Certain Range
    By FallingDown in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-14-2015, 07:38 AM
  2. [SOLVED] Delete all cells in each row except the last used cell then shift left all remaining data
    By dave033 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-16-2014, 09:33 AM
  3. go through a range and delete, shift cells left if contain no data.
    By 13lack13lade in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2014, 09:50 PM
  4. [SOLVED] If criteria met: delete cell and shift to the left but shift only over a certain range
    By olivierpbeland in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-11-2013, 07:44 AM
  5. Replies: 0
    Last Post: 03-26-2013, 02:58 PM
  6. Replies: 3
    Last Post: 02-27-2013, 03:00 PM
  7. shift range of cells to left if find the range of empty do while
    By farrukh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2012, 09:25 AM

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