Selecting Cells (2000)

Topic

New Reply

If I want to select an entire row in a macro I would use
Rows(“2:2”).Select
In my example below the Rows statement isn’t working (syntax error), how would I use variables to select a row. I’m trying to find the last row on one page and then find the last row on the next page and I want to insert whatever the difference in the number of rows is.

Private Sub InsertRows()
Dim LastRowProd As Long
Dim LastRowDaily As Long
Dim NewRow As Long
Sheets(“PRODUCTION”).Select
LastRowProd = Range(“A65536”).End(xlUp).Row
Sheets(“Daily Report”).Select
LastRowDaily = Range(“A65536”).End(xlUp).Row
NewRow = LastRowDaily + 1

Rows(LastRowDaily:LastRowDaily).Select
Selection.Copy
Rows(NewRow:LastRowProd).Select
Selection.Insert Shift:=xlDown

End Sub

Reply | Quote

Replies

As Rows(2).Select is the ame as Rows(“2:2”).Select, you could just use Rows(LastRowDaily).Select. Otherwise you need to use concatenation to create a string from LastRowDaily ( Rows(LastRowDaily & “:” & LastRowDaily )).

I think you also need to change

Rows(NewRow:LastRowProd).Select

to

Range(Rows(NewRow), Rows(LastRowProd)).Select

Andrew C

Reply | Quote

Try

Rows(LastRowDaily & ":" & LastRowDaily).Select
    Selection.Copy
Rows(NewRow & ":" & LastRowProd).Select

HTH

Reply | Quote

Why not revamp ALL your code to this 1 line:

Worksheets("PRODUCTION").Range("A65536").End(xlUp).EntireRow.Copy _
    Destination:=Worksheets("Daily report").Range("A65536").End(xlUp).Offset(1, 0)

Or if you want to INSERT the last line:

Worksheets("PRODUCTION").Range("A65536").End(xlUp).EntireRow.Copy
Worksheets("Daily report").Range("A65536").End(xlUp).Offset(1, 0).Insert shift:=xlShiftDown

Steve

Reply | Quote

The code from Steve (sdckapr) has the advantage of delivering much faster performance.

Reply | Quote

Thanks for all of the help.
Steve’s code works great but it only copies the last line of the Prodction sheet onto the Daily report, the code I have (with the help of unkamunka) copies the last row of the Daily Report (which contains formulas linked to Production) and pastes the formulas in as many rows as required. There could be more than a one row difference between the two sheets.
Thanks again for the quick replies.

Reply | Quote

I misunderstood your code, this will be faster than your code. It does not do all the selecting that you have in your code.

Sub InsertRowsMod()
    Dim LastRowProd As Long
    Dim NewRow As Long
    Dim wksP As Worksheet
    Dim wksD As Worksheet
    
    Set wksP = Worksheets("PRODUCTION")
    Set wksD = Worksheets("Daily Report")
    
    LastRowProd = wksP.Range("A65536").End(xlUp).Row
    NewRow = wksD.Range("A65536").End(xlUp).Offset(1, 0).Row

    wksD.Range("A65536").End(xlUp).EntireRow.Copy
    wksD.Rows(NewRow & ":" & LastRowProd).Insert shift:=xlDown
End Sub

Steve

Reply | Quote

Thanks Steve, that does run faster.

Reply | Quote