Selection.Delete Shift:=xlUp
Excel VBA Tools from Andrew
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Selection.Delete Shift:=xlUp

  1. #1
    New Member
    Join Date
    Sep 2015
    Location
    Bunbury West Australia
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Selection.Delete Shift:=xlUp

     
    I want to delete contents of some cells and then shift the cells up without modifying formulae in the same rows of the deleted cells.

    Currently when I delete the cells and move the cells up to replace the deleted cells, any formulae in the rows of the deleted cells are modifed and reference cells incorrectly. I know I can delete the rows to get the desired result but I am doing many similar deletions which would mean I would have to copy the formulae again many many times.

    Any ideas
    terry87

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    8,221
    Post Thanks / Like
    Mentioned
    146 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Selection.Delete Shift:=xlUp

    Rather than delete the rows, why not clear the contents & then sort.
    Last edited by Fluff; Dec 7th, 2017 at 03:35 PM.
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  3. #3
    New Member
    Join Date
    Sep 2015
    Location
    Bunbury West Australia
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selection.Delete Shift:=xlUp

    Sorting would not help as I need the remaining cells in the same order as they were before.


  4. #4
    New Member
    Join Date
    Sep 2015
    Location
    Bunbury West Australia
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selection.Delete Shift:=xlUp

    btw thnx for quick reply

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    8,221
    Post Thanks / Like
    Mentioned
    146 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Selection.Delete Shift:=xlUp

    Quote Originally Posted by terry87 View Post
    Sorting would not help as I need the remaining cells in the same order as they were before.

    Not a problem, you can add a helper column with the values 1,2,3 etc & then sort on that. That way your data remains in the same order but without the blank rows.
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  6. #6
    New Member
    Join Date
    Sep 2015
    Location
    Bunbury West Australia
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selection.Delete Shift:=xlUp

    Thnx Fluff but I don't understand your solution

  7. #7
    New Member
    Join Date
    Sep 2015
    Location
    Bunbury West Australia
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selection.Delete Shift:=xlUp

    Can you please expand on your suggestion....

    I guess another solution would be to create a macro which selects the columns with the incorrect reference "#Ref!" and replace with correct reference "A1"

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    8,221
    Post Thanks / Like
    Mentioned
    146 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Selection.Delete Shift:=xlUp

    If you had data like this, with the helper col H

    Excel 2013 32 bit
    ABGH
    1PostcodeIn Use?GridRefHelper
    2AL1 5EGYesTL1590701
    3AL10 9WXYesTL2190722
    4AL2 1UXYesTL1660393
    5AL2 2EJYesTL1570174
    6AL2 2EXYesTL1540475
    7AL2 3XZYesTL1290216
    8AL2 3YTYesTL1260167
    9AL3 6ADYesTL1320698

    England postcodes





    After clearing some rows you get

    Excel 2013 32 bit
    ABGH
    1PostcodeIn Use?GridRefHelper
    2AL1 5EGYesTL1590701
    3AL10 9WXYesTL2190722
    4
    5AL2 2EJYesTL1570174
    6AL2 2EXYesTL1540475
    7
    8
    9AL3 6ADYesTL1320698

    England postcodes





    And then sort

    Excel 2013 32 bit
    ABGH
    1PostcodeIn Use?GridRefHelper
    2AL1 5EGYesTL1590701
    3AL10 9WXYesTL2190722
    4AL2 2EJYesTL1570174
    5AL2 2EXYesTL1540475
    6AL3 6ADYesTL1320698
    7
    8
    9

    England postcodes



    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  9. #9
    New Member
    Join Date
    Sep 2015
    Location
    Bunbury West Australia
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selection.Delete Shift:=xlUp


    Fluff that is brilliant. I have spent days on this. Thank you so much

    Terry97

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    8,221
    Post Thanks / Like
    Mentioned
    146 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Selection.Delete Shift:=xlUp

      
    Glad to help & thanks for the feedback
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com