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

[Legacy 330376]

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

   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

 

[oldbrewer]

highlight blank row, edit delete, do it while recording a macro then copy the code

 

[Legacy 330376]

highlight blank row, edit delete, do it while recording a macro then copy the code

Hi,
Is there a code that does this dynamically, because there might be more then one row which needs to be shifted up?

 

[oldbrewer]

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.............

 

[Legacy 330376]

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".

 

[oldbrewer]

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....

 

[MARK858]

What is defining the rows you want to copy/cut/paste?

 

[Legacy 330376]

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?

 

[Legacy 330376]

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.

 

[MARK858]

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.

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