The issue about run-time error 1004 on vba

Question

VBA Run-time error 1004

I'm programmng Excel Macro by VBA. Some problems happened.

There are 3 Excel files. One is old version excel file(extended format : xls) and the others are xlsx format.

I want to get the last row position of each file. My code is below.

Sub macro()

Dim testWorkBook1 As Workbook, testWorkBook2 As Workbook, testWorkBook3 As Workbook
Dim testSheet1 As Worksheet, testSheet2 As Worksheet, testSheet3 As Worksheet
Dim count1 As Integer, count2 As Integer, count3 As Integer

Set testWorkBook1 = Workbooks.Open("D:\test folder\test1.xls")      ' line a --> xls format
Set testWorkBook2 = Workbooks.Open("D:\test folder\test2.xlsx")
Set testWorkBook3 = Workbooks.Open("D:\test folder\test3.xlsx")      'line b

Set testSheet1 = testWorkBook1.Sheets(1)
Set testSheet2 = testWorkBook2.Sheets(1)
Set testSheet3 = testWorkBook3.Sheets(1)

count3 = testSheet3.Cells(Rows.count, 1).End(xlUp).Row
count2 = testSheet2.Cells(Rows.count, 1).End(xlUp).Row
count1 = testSheet1.Cells(Rows.count, 1).End(xlUp).Row    'the point where runtime error 1004 happened 


End Sub

When running code, run-time error 1004 happened. I tried serveral other cases. and i knew 2 things so far.

if test1 file is "xlsx" version , the program runs successfully.
if the 'line a' placed 'line b' below, the program also runs successfully. for example,

Set testWorkBook2 = Workbooks.Open("D:\test folder\test2.xlsx")
Set testWorkBook3 = Workbooks.Open("D:\test folder\test3.xlsx")      'line b
Set testWorkBook1 = Workbooks.Open("D:\test folder\test1.xls")      ' line a --> xls format

I would like to get not only the solution but also the reasons.

Try using countX = testSheetX.Cells(testSheetX.Rows.count, 1).End(xlUp).Row. Especially when working with different workbooks using Rows, Cells etc. can be tricky. — cr44sh
One more note on finding the last row on a sheet. There are several ways to achieve this and not all deliver the same result. You may want to check which one fits best for you, see here for example: thespreadsheetguru.com/blog/2014/7/7/… — cr44sh
You need to declare your row counting variables As Long instead of Integer. Excel has more rows than Integer can handle! I recommend to always use Long instead of Integer as there is no benefit in using Integer in VBA. Also make sure that you use Worksheets and not Sheets. Sheets can be a Chart sheet too and a chart sheet has no .Rows. So if your Sheets(1) is a chart it will fail. Use Worksheets(1) instead. — Pᴇʜ
Even though my poor english, i thanks a lot to everybody who wrote reply. — Sufarm.kr수련농원

Variatus Variatus · Accepted Answer · 2020-03-17T08:36:00

Your syntax is faulty. This is how it should be.

with testSheet1
    count1 = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

The critical difference is in the leading period here: .Rows.Count. This period makes VBA take the rows count from TestSheet1 whereas that count is taken from the ActiveSheet if omitted. Of course, in your example, the count on the ActiveSheet is much larger than in an XLS version worksheet and this causes the failure.

The issue about run-time error 1004 on vba

1 Answers