+ Reply to Thread
Results 1 to 8 of 8

Delete two cells in a row based on value in second cell then shift remaining cells left

  1. 05-28-2022, 09:16 PM #1
    MSchibs
    MSchibs is offline
    Registered User
    Join Date
    05-02-2009
    Location
    Eagan, MN
    MS-Off Ver
    Excel 365
    Posts
    58

    Delete two cells in a row based on value in second cell then shift remaining cells left

    The attached worksheet has 20,000+ rows of information that I get daily. I have to remove the cell and it's corresponding cell based on the second cell being empty and shift everything left. Example in Row 2 Attribute1Name has the text 2nd Ring Face Design in it and Attribute1Value is empty I need to delete both Cells and shift the rest of the row to the left. This process needs to do this all the way through the row condensing down to just cells with a value and it's name to the left of it. Then proceed down the worksheet to the last row.
    Attached Files Attached Files
    Last edited by MSchibs; 05-29-2022 at 10:47 AM.
    Reply With QuoteRegister To Reply
  2. 05-29-2022, 12:08 AM #2
    jolivanes
    jolivanes is offline
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,516

    Re: Delete two cells in a row based on value in second cell then shift remaining cells lef

    Forget about this for a while. Need to do some more checking.
    Last edited by jolivanes; 05-29-2022 at 12:12 AM.
    Reply With QuoteRegister To Reply
  3. 05-29-2022, 12:24 AM #3
    jolivanes
    jolivanes is offline
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,516

    Re: Delete two cells in a row based on value in second cell then shift remaining cells lef

    Looping is not the fastest way of doing it.
    If you can't live with the speed, or lack thereof, let us know
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 05-29-2022, 06:11 AM #4
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,159

    Re: Delete two cells in a row based on value in second cell then shift remaining cells lef

    Looping the cells is gonna take forever...Looping data in memory will be faster...
    Please Login or Register  to view this content.
    Last edited by sintek; 05-29-2022 at 06:25 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!
    Reply With QuoteRegister To Reply
  5. 05-29-2022, 07:21 PM #5
    jolivanes
    jolivanes is offline
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,516

    Re: Delete two cells in a row based on value in second cell then shift remaining cells lef

    @sintek
    It's a coffee break type of code. Master goes for coffee, computer goes to work while master is absent.
    Reply With QuoteRegister To Reply
  6. 05-30-2022, 12:37 AM #6
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,159

    Re: Delete two cells in a row based on value in second cell then shift remaining cells lef

    It's a coffee break type of code...
    That gave me a Chuckle...
    Reply With QuoteRegister To Reply
  7. 05-30-2022, 05:00 AM #7
    k1dr0ck
    k1dr0ck is offline
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    949

    Re: Delete two cells in a row based on value in second cell then shift remaining cells lef

    @sintek

    any advice on how to learn looping data in memory?
    I'm trying to understand your code in post no. 4 and the only line I can understand is the variable declaration line :D
    Reply With QuoteRegister To Reply
  8. 05-30-2022, 05:06 AM #8
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,159

    Re: Delete two cells in a row based on value in second cell then shift remaining cells lef

    @ kldr0ck...
    There are thousands of posts in the forum making use of this...
    This snippet below stores the data...cells(1).currentregion refers all continuous rows and columns...
    Please Login or Register  to view this content.
    This loops the rows...
    Please Login or Register  to view this content.
    This loops the columns
    Please Login or Register  to view this content.
    Check out a few links on Arrays
    1
    2
    3
    Last edited by sintek; 05-30-2022 at 05:10 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Delete two cells in a row based on value in first cell then shift remaining cells left
    By MSchibs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-12-2022, 08:46 AM
  2. Delete and shift left a lot of cells
    By btbrennan81 in forum Excel General
    Replies: 2
    Last Post: 06-22-2016, 06:44 PM
  3. [SOLVED] Delete and shift remaining cells to the top.
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2015, 10:54 AM
  4. [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
  5. Macro to Remove Cells = 0 and Shift Remaining Cells on Row Left w/ One Condition
    By Mark V. in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-07-2013, 12:45 PM
  6. [SOLVED] Find cells which contain specific word, delete cells and shift left.
    By BPSJACK in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-24-2012, 07:00 AM
  7. Delete empty cells and shift remaining left
    By kiboodez in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-25-2010, 02:17 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1