VBA delete row and shift up

1. Jan 21st, 2014, 07:46 AM #1

   Nội dung chính Show

   • [RESOLVED] Excel delete cells shift up
   • Re: Excel delete cells shift up
   • Re: Excel delete cells shift up
   • Re: Excel delete cells shift up
   • Re: Excel delete cells shift up
   • Re: Excel delete cells shift up
   • Re: Excel delete cells shift up
   • Re: Excel delete cells shift up
   • Re: Excel delete cells shift up
   • Re: Excel delete cells shift up
   • Re: Excel delete cells shift up
   • Re: Excel delete cells shift up
   • Re: Excel delete cells shift up
   • Re: [RESOLVED] Excel delete cells shift up
   • Re: [RESOLVED] Excel delete cells shift up
   • Re: [RESOLVED] Excel delete cells shift up
   • How do you delete row and move up in Excel VBA?
   • How do I delete a specific row in VBA?
   • How do you delete a row based on a cell value in Excel VBA?

   [RESOLVED] Excel delete cells shift up

   VS 2012 - Excel 2010
   I'm having trouble trying to delete a range of cells and then shifting up in a loop. Here is my loop:

   Code:

   mRow = 3
   With xl
       Do While CStr(.Cells(mRow, 1).value) <> ""
           If .Cells(mRow, 10).value = "Delete" Then
               mDeleteRange = "A" & mRow & ":J" & mRow
               .ActiveSheet.Range(mDeleteRange).Delete(XlDeleteShiftDirection.xlShiftUp)
               mRow = mRow - 1
           End If
           mRow = mRow + 1
       Loop
   End With

   I've tried a number of variations on XlDeleteShiftDirection.xlShiftUp and even hardcode the enum value. The most prevalent error on my attempts is:

   Delete method of Range class failed

   Any ideas?

   Please remember next time...elections matter!

   ---

2. Jan 21st, 2014, 08:06 AM #2

   Re: Excel delete cells shift up

   I don't have .net on this computer, but in Excel itself, something like this would do it:

   Code:

   Sub delUp()
       Dim ws As Worksheet
       Dim rng As Range
       
       Set ws = ActiveSheet
       Set rng = ws.Range("g1:k3")
       rng.Delete Shift:=xlUp
   End Sub

   ---

3. Jan 21st, 2014, 08:10 AM #3

   Re: Excel delete cells shift up

   Originally Posted by vbfbryce

   I don't have .net on this computer, but in Excel itself, something like this would do it:

   Code:

   Sub delUp()
       Dim ws As Worksheet
       Dim rng As Range
       
       Set ws = ActiveSheet
       Set rng = ws.Range("g1:k3")
       rng.Delete Shift:=xlUp
   End Sub

   Thanks anyway but I'm using .Net and that won't compile.

   Please remember next time...elections matter!

   ---

4. Jan 21st, 2014, 08:20 AM #4

   Re: Excel delete cells shift up

   I know that it won't compile in .net, but would the "rng.delete shift:=xlup" line translate for you (using your own variable names)?

   ---

5. Jan 21st, 2014, 08:29 AM #5

   Re: Excel delete cells shift up

   Originally Posted by vbfbryce

   I know that it won't compile in .net, but would the "rng.delete shift:=xlup" line translate for you (using your own variable names)?

   Maybe we are not talking about the same thing. What you are asking me to do doesn't seem to work (it doesn't fit into .Net). I think I need something like:

   .ActiveSheet.Range(mDeleteRange).Delete(XlDeleteShiftDirection.xlShiftUp)

   where shift up is part of the delete.

   Please remember next time...elections matter!

   ---

6. Jan 21st, 2014, 08:33 AM #6

   Re: Excel delete cells shift up

   my "rng" (a range variable) is the same as your ".activesheet.range(mDeleteRange).

   the "shift:=xlup" is how it looks in Excel, so I'm wondering if it could be like:

   ".activeSheet.Range(mDeleteRange).Delete shift:=xlup"

   ---

7. Jan 21st, 2014, 08:37 AM #7

   Re: Excel delete cells shift up

   Originally Posted by vbfbryce

   my "rng" (a range variable) is the same as your ".activesheet.range(mDeleteRange).

   the "shift:=xlup" is how it looks in Excel, so I'm wondering if it could be like:

   ".activeSheet.Range(mDeleteRange).Delete shift:=xlup"

   I guess I'm just not saying it right. This "shift:=xlup" has to be part of the delete method inside the parenthesis from what I'm seeing. What you are suggesting was one of the very first thing I found on Google and tried. It doesn't work that way in .Net. It is not even close to the right syntax.

   Please remember next time...elections matter!

   ---

8. Jan 21st, 2014, 10:00 AM #8

   Re: Excel delete cells shift up

   I think if you omit the "direction" argument it will shift "up" by default, so:

   .activeSheet.Range(mDeleteRange).Delete

   Works for me.

   ---

9. Jan 21st, 2014, 10:01 AM #9

   Re: Excel delete cells shift up

   I'll give it a try....Thanks!

   Please remember next time...elections matter!

   ---

10. Jan 21st, 2014, 10:02 AM #10

    Re: Excel delete cells shift up

    Seems that an argument of "1" is LEFT, while "2" is UP, so:

    .activeSheet.range(mDeleteRange).Delete(2)

    for your needs.

    ---

11. Jan 21st, 2014, 10:04 AM #11

    Re: Excel delete cells shift up

    Originally Posted by vbfbryce

    Seems that an argument of "1" is LEFT, while "2" is UP, so:

    .activeSheet.range(mDeleteRange).Delete(2)

    for your needs.

    It will take me a bit before I can test but earlier I thought I saw the enum for up was -46nn something.

    Please remember next time...elections matter!

    ---

12. Jan 21st, 2014, 10:32 AM #12

    Re: Excel delete cells shift up

    I tried it with a "1" and a "2" and it seemed to work for shift left and shift up, but I can't find any documentation on it

    ---

13. Jan 21st, 2014, 10:33 AM #13

    Re: Excel delete cells shift up

    It turns out shift up is a default. When I started out I recorded a macro to get the basic code and that is where it made me pick which way the cells should go. I plugged a "2" in there and it didn't crash but since shift up is the default I don't know if that made a difference.

    Thanks vbfbryce. Marks for you!

    Please remember next time...elections matter!

    ---

14. Jan 21st, 2014, 10:37 AM #14

    Re: [RESOLVED] Excel delete cells shift up

    The macro also generated the

    Selection.Delete Shift:=xlUp

    So I'm guessing that is VBA syntax.

    Please remember next time...elections matter!

    ---

15. Feb 7th, 2021, 09:03 AM #15

    New Member

    ---

    Re: [RESOLVED] Excel delete cells shift up

    hi
    .activeSheet.range(mDeleteRange).Delete(XlDeleteShiftDirection.xlShiftUp)

    it's worked for me.

    ---

16. Feb 7th, 2021, 11:51 AM #16

    Re: [RESOLVED] Excel delete cells shift up

    Hello,

    The thread is 7 years old... I guess he found an answer...

    The best friend of any programmer is a search engine

    
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    �They did not know it was impossible so they did it� (Mark Twain)

    ---

How do you delete row and move up in Excel VBA?

How do I delete a specific row in VBA?

How do you delete a row based on a cell value in Excel VBA?