CODEWITHSUNDEEP
excelms-accessvba
Stefano
Stefano

Reputation: 199

Access VBA: get the last row of an Excel sheet

With Access VBA, I am getting some data from an Excel file to move to an Access table.

I get

Application-defined or Object-defined error

on this code line:

For Each c1 In mSheet.Range(mSheet.Range("F4"), mSheet.Range("F4").End(xlDown)).Cells

The issue is surely due to .End(xlDown): I have tried to remove .End(xlDown) and it works.

As an alternative, I used this other code but nothing changed:

Dim mRow As Integer
mRow = mSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

In this case, I get

Error 1004: Unable to get the SpecialCells property of the Range class.

Upvotes: 0

Views: 866

Answers (1)

Rory
Rory

Reputation: 34045

Unless you have a reference set to the Excel object library, xlDown has no value. Add:

Const xlDown as Long = -4121

to your code. Though generally it is better to work up from the bottom of the sheet.

Upvotes: 1

Related Questions