setting the last row for a macro

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

 

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

Thanks.  This does work but I noticed that it adds an additional vlookup in the last "blank" row.

Any suggestions?

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

After I used the helpping info from Dave Peterson, the macro works very well.

 

Thanks

Answer 9

Your Macro did work and I thank you for your help.