Excel VBA Range until last available cell

user_9433566 0
Viewed 1129

I have problems implementing a VBA code using "Range":

I'm using 2 sheets. The first one is "Import" and the other "Data", where I'm using the data from "Import", using different formulas in order to edit/filter my data. The data from the "Import" sheet changes weekly, so the number of rows (5000-10000 rows).

I put the Range until 10000, to always cover my data, but when the rows are under 10000, it places N/A on the cells, which has to be deleted every time.

This is what I use on the "Data" sheet (each column from Data imports other column from Import):

Range("A2:A10000").Formula = "='Import'!RC"
Range("B2:B10000").Formula = "=VLOOKUP(RC[-1],'Import'!R1C[-1]:R10000C[43],45,FALSE)"`

Is there any way to make the Range see how many rows are on each column on "Import", so I can avoid all the N/A?

excel-formula vba excel
3 Answers

Try,

dim lr as long

with worksheets("Import")
    lr = .cells(.rows.count, "A").end(xlup).row
end with

with worksheets("Data")
    .Range(.cells(2, "A"), .cells(lr, "A")).Formular1c1 = "='Import'!RC"
    .Range(.cells(2, "B"), .cells(lr, "B")).Formular1c1 = _
         "=VLOOKUP(RC[-1], 'Import'!C1:C45, 45, FALSE)"
end with

Is it really necessary to bring the column A values over? Seems like the formula in column B could just as easily be,

with worksheets("Data")
    .Range(.cells(2, "B"), .cells(lr, "B")).Formular1c1 = "=RC45"
end with
Admin1

I think it's better to look at the last row and then only apply to the relevant rows.

But you can wrap the VLOOKUP in IFERROR() function. So when no values exists it will show the #N/A as blank cells... 

Range("A2:A10000").Formula = "='Import'!RC"

Range("B2:B10000").Formula = "=IFERROR(VLOOKUP(RC[-1],'Import'!R1C[-1]:R10000C[43],45,FALSE),"""")"
user_45525290

I strongly recommend to use think kind of approach to avoid some unexpected errors, also it is easiest to understand the code and to modify for your next challenge.

Dim tw As Workbook: Set tw = ThisWorkbook
Dim shImp As Worksheet: Set shImp = tw.Worksheets("Import")
Dim shData As Worksheet: Set shData = tw.Worksheets("Data")
Dim nrRows As Long

nrRows = shImp.Cells(shImp.Rows.Count, "A").End(xlUp).Row
shData.Range("A2:A" & nrRows).FormulaR1C1 = "=Import!RC"
shData.Range("B2:B" & nrRows).FormulaR1C1 = "=VLOOKUP(RC[-1], 'Import'!C1:C45, 45, FALSE)"
user_46887870
Related