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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
highlight blank row, edit delete, do it while recording a macro then copy the code
 
L

Legacy 330376

Guest
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

Board Regular
Joined
Apr 11, 2010
Messages
11,005
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.............
 
L

Legacy 330376

Guest

ADVERTISEMENT

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

Board Regular
Joined
Apr 11, 2010
Messages
11,005
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,841
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

What is defining the rows you want to copy/cut/paste?
 
Last edited:
L

Legacy 330376

Guest
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?
 
L

Legacy 330376

Guest
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,841
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

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
Top