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

 

[Legacy 330376]

Hi Mark,
Brilliant- the "XXX delete" code works.
One Quick question, the column width of column A reduces from 16 to 4.71 when the code is run.
Is there a line of code, I can prevent the column width reducing?

Thanks

Hi Mark,
Don't worry, I have added a line of code in the end of the If syntax and it works.

  Columns("A").ColumnWidth = 20

 

[MARK858]

Change the red A to a B?

Sheets("Risk").Columns("[COLOR="#FF0000"]A[/COLOR]:W").AutoFit

 

[Legacy 330376]

Hi Mark,

I just realised I might need to amend this code, and use it in another macro button.

If I want cells A4 onwards to equal "risk", where would I define it in the code?

Sub XXXdelete()
    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:A" & LastRow).EntireRow
            .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

Thanks

 

[MARK858]

I don't understand your question, are you just saying you want A4 to last row to equal "risk".

 

[Legacy 330376]

I don't understand your question, are you just saying you want A4 to last row to equal "risk".

Hi Mark, ignore the question I had previously.

With the below code, I want the results to be pasted into column B onwards.
See the part in bold. When I change it to B, it does not work

Sub XXXdelete()
    Dim LastRow As Long
    Dim destRng As Range

    Application.ScreenUpdating = False


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

 

[MARK858]

You can't paste an entire row into column B, an entire row goes from Column A to Column XFD (in 2007 and later) and so an entire row is obviously 1 column more than if you start the paste in Column B so will error.

Do you care to post the columns you are dealing with or are the columns dynamic (if dynamic state what determines the last column)?

 

[Legacy 330376]

You can't paste an entire row into column B, an entire row goes from Column A to Column XFD (in 2007 and later) and so an entire row is obviously 1 column more than if you start the paste in Column B so will error.

Do you care to post the columns you are dealing with or are the columns dynamic (if dynamic state what determines the last column)?

So in the "Risks" spreadsheet I have columns A to V, for argument sake lets say they are named colA to ColV.
What they currently do is they get copied into archive tab(last blank row available in the worksheet, so there is no overwriting issues) into columns A to V (titled colA to ColV) from the code you kindly worked on and any empty cells in the "risks" spreadsheet are shifted up- this is tested and successful.

However I want on the "archive tab" to have a new column in column A called ("archive?") and then ColA to ColV will begin from Column B onwards.
If in the archive? column, the user enters yes the row will be cut and pasted a new tab called "permanentA" and then using the principles of the original code you supplied to shift empty cells up in the "archive tab". Unless you know of an easier interactive way the user can move a row if the column "archive?" has a value "Yes".

The "permanentA" tab will have headers so any new rows will be added from row 4 onwards. Also the same principles in terms of last available row will be used.

I was thinking to have the "archive?" column at column W in the "archive tab", but it wont be user friendly as the user will have the move the cursor to the end of the workbook. I want to make it easy for the user to use.

Any suggestions would be great.

 

[MARK858]

I'll take a look at this at some stage over the weekend as

the user enters yes the row will be cut and pasted a new tab called "permanentA"

means a total rewrite of the code as it is now requires worksheet.change code and being honest I don't feel like writing worksheet.change code at the moment.

For future reference it saves everybody's time if you actually ask for what you want in the first post

If anybody wants to jump in before me please you are more than welcome

 

[Legacy 330376]

Hi mark,

Thanks.

Its a new requirement, hence i messaged again. I will do my own rewrite aswell, and send you my code once i completed. I appreciate your time