+ Reply to Thread
Results 1 to 4 of 4

Code that clears cells but needing to shift cells up with formatting

  1. #1
    Forum Contributor
    Join Date
    05-01-2018
    Location
    Maine
    MS-Off Ver
    2010
    Posts
    114

    Code that clears cells but needing to shift cells up with formatting

    Hello,

    This forum is extremely helpful so thank you all in advance. I have a code that is meant to erase a row based on "True" in column Y and move the contents to another sheet (for archiving purposes). This code currently just clears the row on the "Main" sheet- erasing everything in the row. I was wondering the best way to modify this to just erase (not cut or delete) cells A:X and then shift the next cells up to replace the A:X that was cleared. This is so there is not a blank unformatted row in the middle of my dataset.

    I have tried to change it but I seem to be stuck at the
    Please Login or Register  to view this content.
    area. I tried to change that to cells(r.cells) but it doesnt seem to work to just erase A:X and im not entirely sure how to define the part where it moves the next row up? Any help would be greatly appreciated.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Code that clears cells but needing to shift cells up with formatting

    See if this will do it.
    Please Login or Register  to view this content.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Forum Contributor
    Join Date
    05-01-2018
    Location
    Maine
    MS-Off Ver
    2010
    Posts
    114

    Re: Code that clears cells but needing to shift cells up with formatting

    Thank you so much for the assistance. That modification worked like intended, however, I am running into one small issue. I didn't consider that Column X has a checkbox in it. When deleted it shifts the below checkbox up over the other. Do you know of a way to simply untick the checkbox in column X on the row that is being deleted?

    I am thinking I can change the cells deleted from A:X to A:W and simply untick the box in column X.

    Again, thank you so much for the assistance. I always forget about the .resize part.

  4. #4
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Code that clears cells but needing to shift cells up with formatting

    I don't know enough about how you have deployed the checkboxes to offer any code for it. But the idea of reducing the length of the resize is one way to avoid changing the checkbox location.
    Regards, JLG
    Last edited by JLGWhiz; 09-22-2020 at 02:41 PM.

+ 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] code multiple cells and clears the contents but get Method Range of object_ Global failed
    By dyoung785 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 06-01-2019, 10:54 PM
  2. [SOLVED] How do I speed up VBA code that clears cells
    By Bobbbo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-03-2017, 07:05 PM
  3. Needing a code to be able to do running totals for multiple cells.
    By waxsiwalas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-03-2016, 10:06 AM
  4. vba code to clears cells and hide rows
    By Zimmerman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-04-2015, 10:09 AM
  5. Move cells based on cell length *shift cells right) - code required
    By pauldaddyadams in forum Excel Programming / VBA / Macros
    Replies: 49
    Last Post: 11-21-2014, 08:56 AM
  6. [SOLVED] Macro code needed to shift cells right
    By mohammedfaizal in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-14-2013, 09:05 AM
  7. [SOLVED] Userform TextBox Problem... formatting code clears data instead of formatting
    By michaeljoeyeager in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-26-2012, 11: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