Shifting particular Range of cells in a row upwards by a delete button but without disturbing a defined row which should not move upwards and contents

NimishK · Mar 9, 2018

Hi Anyone

My range is from "C2 : F16"

As of now i am deleting particular range of cells in a row.
i've used the following code to delete the particular range of cells in a row which is working fine
<code>

Code:

Option Expilict

Public curRow as integer

Private Sub UserForm_Initialize()
curRow = 2

With Sheets("DealerDetails")
    .Activate
text1.Text = .Cells(curRow, 3).Value   
text2.Text = .Cells(curRow, 4).Value 
text3.Text = .Cells(curRow, 5).Value 
text4.Text = .Cells(curRow, 6).Value 

End with
End sub

Private Sub cmdDelete_Click()
    With Sheets("Sheet2")
        If .Range("C" & curRow) = "" Then
                  Application.Cells(curRow, 3).Delete Shift:=xlUp
                  Application.Cells(curRow, 4).Delete Shift:=xlUp
                  Application.Cells(curRow, 5).Delete Shift:=xlUp
                  Application.Cells(curRow, 6).Delete Shift:=xlUp
       Else
                  Application.Cells(curRow, 3).Delete Shift:=xlUp
                 Application.Cells(curRow, 4).Delete Shift:=xlUp
                 Application.Cells(curRow, 5).Delete Shift:=xlUp
                 Application.Cells(curRow, 6).Delete Shift:=xlUp
        End If       
    End with     

End Sub
</code>

What i want is whenever i delete the range of particular cells of that particular row using above code. i dont want any rows below row 17 to shift upwards.
How can i lock this. Only cells should shift upwards between range "C2: F16" for eg if i am in row 6 i.e "C6: F6" i delete all the contents here
then from range "C7:F16" should only shift upwards.
But the Contents from "A17 to G17" should remain intact in row 17. and rows below should remain intact too. Any possibility

Thanks NimishK

wideboydixon · Mar 9, 2018

Might be easier to copy cells up and clear the contents of the last row:

Code:

Private Sub cmdDelete_Click()

With Sheets("Sheet2")
    If .Range("C" & curRow) = "" Then
        If curRow < 16 Then .Range("C" & curRow + 1 & ":F16").Copy Destination:=.Cells(curRow, "C")
        .Range("C16:F16").ClearContents
    Else
        If curRow < 16 Then .Range("C" & curRow + 1 & ":F16").Copy Destination:=.Cells(curRow, "C")
        .Range("C16:F16").ClearContents
    End If
End With

End Sub

Both code paths do the same thing; not sure why.

WBD

NimishK · Mar 9, 2018

Thanks WBD for the suggestion. Is this the only and easiest method ?

Did think of copying the contents of current row (or deleted row) to the row above and clearing contents of last row before posting my post. if this the only limitation then will go with the logic of copying and clearing contents.

somehow not willing to let go this easily as i've hold on to it.

Both code paths do the same thing; not sure why.

Agreed. to have single path.

wideboydixon · Mar 9, 2018

It's the most logical solution. If you want everything in C2:F16 to scroll up but other rows to remain static the copy/paste/clear method is the easiest.

WBD

NimishK · Mar 12, 2018

Actually whats happening is copying the record of next record. so no i have two similar records in row 6 and row 7. Ideally the 7th row record should shift upwards in row 6.
somehow not happeing. what is displayed in 6th row and 7th row is the data in 7th row diplayed and same in 6th row too

NimishK · Mar 12, 2018

nothing

wideboydixon · Mar 12, 2018

Depends on the value of curRow ...

WBD

NimishK · Mar 13, 2018

Though depending on value of current row. the other data should shift upwards. as per your suggestion it only copies the data in current row and data below does not shift upwards. Please check

wideboydixon · Mar 13, 2018

Before:

Book1

C

D

E

F

1

HEADER

2

$C$2

$D$2

$E$2

$F$2

3

$C$3

$D$3

$E$3

$F$3

4

$C$4

$D$4

$E$4

$F$4

5

$C$5

$D$5

$E$5

$F$5

6

$C$6

$D$6

$E$6

$F$6

7

$C$7

$D$7

$E$7

$F$7

8

$C$8

$D$8

$E$8

$F$8

9

$C$9

$D$9

$E$9

$F$9

10

$C$10

$D$10

$E$10

$F$10

11

$C$11

$D$11

$E$11

$F$11

12

$C$12

$D$12

$E$12

$F$12

13

$C$13

$D$13

$E$13

$F$13

14

$C$14

$D$14

$E$14

$F$14

15

$C$15

$D$15

$E$15

$F$15

16

$C$16

$D$16

$E$16

$F$16

17

DO

NOT

MOVE

THIS

Sheet2

Then I ran:

Code:

Public Sub TestDelete()

curRow = 6
Call cmdDelete_Click

End Sub

After:

Book1

C

D

E

F

1

HEADER

2

$C$2

$D$2

$E$2

$F$2

3

$C$3

$D$3

$E$3

$F$3

4

$C$4

$D$4

$E$4

$F$4

5

$C$5

$D$5

$E$5

$F$5

6

$C$7

$D$7

$E$7

$F$7

7

$C$8

$D$8

$E$8

$F$8

8

$C$9

$D$9

$E$9

$F$9

9

$C$10

$D$10

$E$10

$F$10

10

$C$11

$D$11

$E$11

$F$11

11

$C$12

$D$12

$E$12

$F$12

12

$C$13

$D$13

$E$13

$F$13

13

$C$14

$D$14

$E$14

$F$14

14

$C$15

$D$15

$E$15

$F$15

15

$C$16

$D$16

$E$16

$F$16

16

17

DO

NOT

MOVE

THIS

Sheet2

I'm failing to see whats not working.

WBD

NimishK · Mar 16, 2018

thanks Very much. Was error in my coding.
Thank you once more

Shifting particular Range of cells in a row upwards by a delete button but without disturbing a defined row which should not move upwards and contents

NimishK

Well-known Member

Excel Facts

wideboydixon

Well-known Member

NimishK

Well-known Member

wideboydixon

Well-known Member

NimishK

Well-known Member

NimishK

Well-known Member

wideboydixon

Well-known Member

NimishK

Well-known Member

wideboydixon

Well-known Member

NimishK

Well-known Member

Similar threads

Forum statistics

Share this page

Shifting particular Range of cells in a row upwards by a delete button but without disturbing a defined row which should not move upwards and contents

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock