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
Joined
Sep 4, 2015
Messages
688
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
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Before:


Book1
CDEF
1HEADERHEADERHEADERHEADER
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
17DONOTMOVETHIS
Sheet2


Then I ran:
Code:
Public Sub TestDelete()

curRow = 6
Call cmdDelete_Click

End Sub

After:

Book1
CDEF
1HEADERHEADERHEADERHEADER
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
17DONOTMOVETHIS
Sheet2


I'm failing to see whats not working.

WBD
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,619
Members
452,786
Latest member
k3calloway

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top