VBA- How to shift cells up once you cut a row into new spreadsheet ??

L

Legacy 330376

Guest
Hi Guys,

I am trying to cut and paste rows from a worksheet(risk) into another worksheet(archive).
This works, however I want to shift the empty row up from the (risk) worksheet, without losing my formatting/ formulas. Any ideas?
Thanks in advance


Code:
   Dim LastRow As Long
    Dim destRng As Range
    
    Application.ScreenUpdating = False
    
    With Sheets("Archive")
    Set destRng = .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row + 1)
    
    LastRow = Sheets("Risk").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Risk").Range("A4:W" & LastRow).Cut Destination:=destRng
    .Columns("A:W").AutoFit
    End With

    Application.ScreenUpdating = True
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
your macro will hopefully cut the row, paste it elsewhere, then return and delete the row - so you select a row run the macro, select another row, run the macro again.............
 
Upvote 0
Hi,

Thanks for your reply but its not feasible for what i want. It deletes the headers, if there is just one line of data.

There must be a code i can add to my existing one, which can cut the row and shift the empty cell up based on the amount of rows which were cut from "risk" worksheet to "archive".
 
Upvote 0
if you only had headers and no data you would not want to delete a row
with one row(2) with data delete it and no need to worry
with 5 rows of data delete third row you now have 4 rows of data

you now say you may delete a blocjk of say 10 rows

highlight all 10, cut, go to sheet 2, paste, back to sheet 1 highlight the 10 empty rows, edit delete

I cannot see your problem - maybe I am missing something....
 
Upvote 0
What is defining the rows you want to copy/cut/paste?
 
Last edited:
Upvote 0
if you only had headers and no data you would not want to delete a row
with one row(2) with data delete it and no need to worry
with 5 rows of data delete third row you now have 4 rows of data

you now say you may delete a blocjk of say 10 rows

highlight all 10, cut, go to sheet 2, paste, back to sheet 1 highlight the 10 empty rows, edit delete

I cannot see your problem - maybe I am missing something....

Row 3 has headers in the "risk" spreadsheet. If there is data from row 4 to 100, i want to cut and paste it to "archive spreadsheet" from row2 onwards. The only part i want to add to the code is for the rows to shift up and no blank rows in the "risk" spreadsheet. Does this make sense?
 
Upvote 0
Hi all, Anyone has any ideas on how i can shift the cells up from my "risk" spreadsheet, when i cut the row into the "archive" worksheet.

Code is in the original post. There may be times when there may be more then one row that needs to be cut in to the "archive" worksheet from "risk" spreadsheet, so if it can be done dynamically that would be great.
 
Upvote 0
Test the code below on a copy of your data. I have kept it as columns A:W as I am not entirely sure by your description if you are dealing with set columns or entire rows.

Code:
Sub XXX()
    Dim LastRow As Long
    Dim destRng As Range

    Application.ScreenUpdating = False


    LastRow = Sheets("Risk").Range("A" & Rows.Count).End(xlUp).Row
    
    If LastRow > 3 Then
        
        With Sheets("Risk").Range("A4:W" & LastRow)
            .Copy Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
            .Delete Shift:=xlUp
        End With
        
    Sheets("Risk").Columns("A:W").AutoFit
    End If
    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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