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,

The headers are the same for both worksheets. If column A is populated in risks spreadsheet, then it cuts and pastes to archive spreadsheet and the rows shifts up, to avoid empty rows being seen. Does it make sense?

 

[MARK858]

Does it make sense?

No because you stated

If there is data from row 4 to 100, i want to cut and paste it to "archive spreadsheet" from row2 onwards

Explain in plain English where or what defines what gets copied from (including is the 100 fixed or a last row with data), where exactly it goes in the destination sheet and whether it is set columns or entire rows being moved.

 

[Legacy 330376]

Hi mark,

Apologies for the confusion.

If column a from rows 4 to 100 is populated in risks spreadsheet. The entire row should be cut and pasted into archive spreadsheet, where the last row of data is blank(because there would be existing data on there)


Risks spreadsheet has the first 3 columns with titles and headers.

Archive spreadsheet only has headers in row1.

The aim is for the rows in risks spreadsheet which is cut, should have its rows shifted up to avoid empty rows being shown to the user.

I hope this is clear now.

 

[MARK858]

If you shift up the rows then you will still see empty cells, do you mean you want the rows hidden?

 

[MARK858]

Please note that the last question is there because your original code was using a last row or are you saying that you want the rows deleted up to row 100 and there is other data below it?

and

where the last row of data is blank(because there would be existing data on there)

Makes no sense to me, it is either blank or there is data (even if it empty strings from a formula it ain't blank)

 

[Legacy 330376]

Please note that the last question is there because your original code was using a last row or are you saying that you want the rows deleted up to row 100 and there is other data below it?

and



Makes no sense to me, it is either blank or there is data (even if it empty strings from a formula it ain't blank)

Hi,

In risk spreadsheet, it has currently 100 rows as column B contains a unique ID. So the situation i had with the present code is with 2 rows needed to be moved to archive, it would leave one row completely blank. Lastrow would be good to use as a dynamic code.

Regarding your second point, if data gets moved to archive it wont overwrite existing data on there, so this is what i meant that it will move to the next empty row.

Example-

Risks- i move two rows
Archive- already there 5 rows for example, these two new rows will start on number 6 onwards.

Hope this is clear now

 

[MARK858]

Risks- i move two rows

Sigh, Back to square one what would determine it is 2 rows to move? unless I know this I can't code for anything. Try and be clear as in your original code it was a straightforward row 4 to last row with data in Column A (which is what the code I posted also does).

Please note this was asked back in post #7

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

and what do you want done with the space left behind in the rows. Do you want them Hidden or deleted?

Archive- already there 5 rows for example, these two new rows will start on number 6 onwards.

The code I posted already does this based on column A (unless you think different?)

Please answer the questions carefully as this is taking too many posts for a simple task

 

[Legacy 330376]

Sigh, Back to square one what would determine it is 2 rows to move? unless I know this I can't code for anything. Try and be clear as in your original code it was a straightforward row 4 to last row with data in Column A (which is what the code I posted also does).

Yes- data in column A from row. A4 onwards.

Please note this was asked back in post #7


and what do you want done with the space left behind in the rows. Do you want them Hidden or deleted?

Deleted

The code I posted already does this based on column A (unless you think different?)

Thanks- will test code tomorrow.

Please answer the questions carefully as this is taking too many posts for a simple task

Hi mark,
I appreciate your time and patience.
I will test your code tomorrow and get back to you.

Sorry for the confusion with my code.

 

[MARK858]

Code for you to test in case you need hide rather than delete and code below that is the same as in post #17 just as is for entire row as you have cleared that up.

Sub XXXHide()
    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)
            .Clear
            .Hidden = True
        End With

    Sheets("Risk").Columns("A:W").AutoFit

End If
With Application
    .Goto Reference:=Sheets("Risk").Range("A1"), Scroll:=True
    .ScreenUpdating = True
End With

End Sub

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

 

[Legacy 330376]

Code for you to test in case you need hide rather than delete and code below that is the same as in post #17 just as is for entire row as you have cleared that up.

Sub XXXHide()
    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)
            .Clear
            .Hidden = True
        End With

    Sheets("Risk").Columns("A:W").AutoFit

End If
With Application
    .Goto Reference:=Sheets("Risk").Range("A1"), Scroll:=True
    .ScreenUpdating = True
End With

End Sub

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

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