How to define range's end point as the last row with data?

Question

I am using vlookup, after finding it with lookup i am using autofill. In macro after the autofill code it finds the range and autofills it. But in terms of code's robustness it is not usefull because the range it finds sticks to the code. Basically,

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Sheet1!C[-5]:C[4],7,FALSE)"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F502")
    Range("F2:F502").Select

is the original code, i just want to do Range("F2: last data")

Thanks in advance

Edit: Solved.

Dim LastRow As Long

LastRow = Cells(Rows.Count, "F").End(xlUp).Row   


Range("F2").Select


        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Sheet1!C[-5]:C[4],7,FALSE)"
       Range("F2").Select
        Selection.AutoFill Destination:=Range("F2", Cells(LastRow, 6))

I was trying to simplify the code but I guess I need to put this before every VLOOKUP

Shai Rado · Accepted Answer · 2016-07-29T11:10:02.760

0

Find the last row used in Column F.

Edit 1: added LastCol (from row 2 in this example))

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Sheet1!C[-5]:C[4],7,FALSE)"
Range("F2").Select

Dim LastRow As Long
Dim LastCol As Long

LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, LastCol).End(xlUp).row

' assuming your data starts from row 2
Selection.AutoFill Destination:=Range(Cells(2, LastCol), Cells(LastRow, LastCol))
Range(Cells(2, LastCol), Cells(LastRow, LastCol)).Select

edited Jul 29 '16 at 11:10

answered Jul 29 '16 at 08:35

Shai Rado

33,032
6
29
51

Thank you for the reply, for finding last row, how can i automize the "F" part. Because data is really big. – hakandeep Jul 29 '16 at 08:44
it does not automate the F actually, in order to automate there shouldn't be any F's in the code. With this code it is not working even for autofill. But thanks for trying and for your time – hakandeep Jul 29 '16 at 10:56
@hakandeep it works when you have data in Column F , don't you ? – Shai Rado Jul 29 '16 at 10:59
Cells and lastrow not working. It finds lastrow as 1. – hakandeep Jul 29 '16 at 11:02

score 0 · Answer 2 · edited May 23 '17 at 11:59

0

You need to find the last row with Rows.Count and you can simplify a little by setting your range so you do not need to use Autofill:

Dim MyRange as Range: Set MyRange = Range("F2:F" & Cells(Rows.Count, "F").End(xlUp).Row)

MyRange.FormulaR1C1 = "VLOOKUP(RC[-5],Sheet1!C[-5]:C[4],7,FALSE)"

Also, you should try to avoid using SelectCheck here

edited May 23 '17 at 11:59

Community

1
1

answered Jul 29 '16 at 08:38

Rémi

372
3
8

Thank you for the reply, but i need to use select. Because data is transferred from sheet to sheet and I am using End property. I don't have much time also so generally I am using macro recording and trying to increase the code's flexibility with modifications. Also I think this code misses the changing cells within the formula since it searches for C2,C3,.. – hakandeep Jul 29 '16 at 08:49
Also code only prints F2 and F3 as vlookup name. It should be taken the data from F column from sheet1 to vlookup on F column on sheet 2. I put sheet1.range=("F2:F" but i think this part is not working. That is my all problem. I want it from F2 to last row that contains data – hakandeep Jul 29 '16 at 11:08

How to define range's end point as the last row with data?

2 Answers2