Find the last row/column in excel using access VBA

user_16510177 0
Viewed 606

I am trying to run a for loop from start to end of all of the data in my excel sheet that is being processed by access vba. I have tried:

myWorksheet.Cells(Rows.Count, 4).End(xlUp).row

but it did not work. I am confused how I can get a one number output so that it is able to run in my loop successfully. Please help. Also, if you could break down the format of your answer that would really help me. Thanks

for-loop vba excel loops
2 Answers

Access has not constant xlUp which has value -4162 in Excel. In Access xlUp is non-declared variable with Variant type and initial value Empty. To use proper End(xlUp) in Access VBA you can write .End(-4162):

myWorksheet.Cells(myWorksheet.Rows.Count, 4).End(-4162).row
user_150353140

I usually define my LastRow like so, with "N" that's just the column that has the data in it, you can adjust that as needed:

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

I usually pair that with a Dim row_no As Long and use it in the loop like so, the hard number here being the first row of my dataset.

For row_no = 5 to LastRow
   'Do the thing
Next

There's way to iterate through loops in reverse order, which makes more sense when you're deleting rows, because when you delete it row it messes with what row the macro thinks it's on. Ie: you delete row 2, so row 3 moves to row 2, but the macro moves to row 3 which has the 4th row of data. For those you'll approach it like so:

For x = LastRow To 5 Step -1
    'Do the thing
Next x
user_118200460
Related