Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting Cells (2000)

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Selecting Cells (2000)

    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

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Cells (2000)

    Try<pre>Rows(LastRowDaily & ":" & LastRowDaily).Select
    Selection.Copy
    Rows(NewRow & ":" & LastRowProd).Select</pre>

    HTH
    Gre

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Selecting Cells (2000)

    Why not revamp ALL your code to this 1 line:

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


    Or if you want to INSERT the last line:
    <pre>Worksheets("PRODUCTION").Range("A65536").End( xlUp).EntireRow.Copy
    Worksheets("Daily report").Range("A65536").End(xlUp).Offset(1, 0).Insert shift:=xlShiftDown
    </pre>



    Steve

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Cells (2000)

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

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Cells (2000)

    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.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Selecting Cells (2000)

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

    <pre>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
    </pre>


    Steve

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Cells (2000)

    Thanks Steve, that does run faster.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •