locked
setting the last row for a macro RRS feed

  • Question

  • I am working on my first macro where the last row changes based on the data be copied from another source.

    I used the macro recorder to get what I have so far.  The problem is that If I have data greater than Row A800 (in this case) then it does not get the results from the vlookup.

     

    How can I set the last row as a variable munber (or how to determine the last row and put it into the statement

     

    Range("A2").Select
        Selection.Clear
        Range("A2").Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],lookup,3,FALSE)"
        Range("A2").Select
        Selection.AutoFill Destination:=Range("A2:A800")
        Range("A2:A800").Select

     

    Tuesday, March 29, 2011 4:19 PM

Answers

  • Replace:

    Selection.AutoFill Destination:=Range("A2:A800")
    Range("A2:A800").Select

    with:

    n = Cells(Rows.Count, "A").End(xlUp).Row
    Selection.AutoFill Destination:=Range("A2:A" & n)
    Range("A2:A" & n).Select


    gsnu201103
    • Marked as answer by wrt1105 Wednesday, March 30, 2011 6:01 PM
    Tuesday, March 29, 2011 4:58 PM

All replies

  • Replace:

    Selection.AutoFill Destination:=Range("A2:A800")
    Range("A2:A800").Select

    with:

    n = Cells(Rows.Count, "A").End(xlUp).Row
    Selection.AutoFill Destination:=Range("A2:A" & n)
    Range("A2:A" & n).Select


    gsnu201103
    • Marked as answer by wrt1105 Wednesday, March 30, 2011 6:01 PM
    Tuesday, March 29, 2011 4:58 PM
  • Hi

    The code below will work if the cells below have content.

    Range("A2").Select
    Selection.Clear
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],lookup,3,FALSE)"
    Range("A2").Select

    Range(Selection, Selection.End(xlDown)).FillDown

    Range("A2:A800").Select

    This will work if there is not.

    Sub test2()
    Dim TotalRows As Long


    Range("A2").Select
    Selection.Clear
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],lookup,3,FALSE)"
    Range("A2").Select

    TotalRows = Selection.CurrentRegion.Rows.Count - 1

    Range(Selection, Selection.Offset(TotalRows, 0)).FillDown

    Range("A2:A800").Select


    End Sub

     


    G North
    Tuesday, March 29, 2011 5:09 PM
  • Thanks for the suggestion.

    I replaced the code but the autofill did not work.  The error message is autofill method of Range class failed.  When I select Debug, It highlights the autofill line.

    Tuesday, March 29, 2011 7:35 PM
  • Thanks.  This does work but I noticed that it adds an additional vlookup in the last "blank" row.

    Any suggestions?

    Tuesday, March 29, 2011 7:43 PM
  • I don't understand why you'd use column A to determine the last row -- wouldn't
    it be empty?

    I'd use column B and something like:

    Dim LastRow as long
    with activesheet
       lastrow = .cells(.rows.count,"B").end(xlup).row
       .range("a2:A" & lastrow).formular1c1 = "=VLOOKUP(RC[1],lookup,3,FALSE)"
    End with

    It doesn't actually fill down the range -- it just fills the range in a single
    assignment.

    Kind of like if you selected the range, wrote the formula for the activecell and
    hit ctrl-enter to fill the selection.

    wrt1105 wrote:


    I am working on my first macro where the last row changes based on the data be copied from another source.

    I used the macro recorder to get what I have so far.  The problem is that If I have data greater than Row A800 (in this case) then it does not get the results from the vlookup.



    How can I set the last row as a variable munber (or how to determine the last row and put it into the statement



    Range("A2").Select
        Selection.Clear
        Range("A2").Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],lookup,3,FALSE)"
        Range("A2").Select
        Selection.AutoFill Destination:=Range("A2:A800")
        Range("A2:A800").Select

    --

    Dave Peterson

    Tuesday, March 29, 2011 9:29 PM
  • Hi

    I assume your refering to the second example Sub Test2().  You obviously have a row above A2 with data in it (not specified in your initial post and I should have assumed you had). In this case

    TotalRows = Selection.CurrentRegion.Rows.Count - 2

    1 for the current row and 1 for the headings.

    Not sure what you mean about the Autofill method.  The examples I provided use FillDown.

    Hope this helps


    G North MMI
    Wednesday, March 30, 2011 7:46 AM
  • Dave,

     

    You were right with the Use of Column B as the source to count.  I ended up using the suggestion from GNS201103 as it was fewest lines.

    Thank you for the help.

    Wednesday, March 30, 2011 6:00 PM
  • After I used the helpping info from Dave Peterson, the macro works very well.

     

    Thanks

    Wednesday, March 30, 2011 6:01 PM
  • Your Macro did work and I thank you for your help.

    Wednesday, March 30, 2011 6:03 PM