Finding the Last Row in worksheet

[DCrake]

Now this is not like me, posing a question, but I thought its about time I codes the app correctly.

My app opens up any given spreadsheet. The first task is to establish the last row used in the spreadsheet. Normally you would loop until there is nothing in a cell in column A, whatever.

However the way the spreadsheets are provided there may be pleanty of blank rows. I have tried using the Edit GoTo Special LastCell and converting it to a macro

But when I run this VB it does not like it for some reason.

I am sure someone out there has come up with a solution by now.

CodeMaster:

 

[neileg]

A bit clunky, but this might work even though I don't know how to code it!

Go to the last row on the sheet in a column that you know will be populated. Then go End Up Arrow. As an Excel macro this looks like this:

Application.Goto Reference:="R65536C1"
    Selection.End(xlUp).Select

 

[The_Doc_Man]

If you open the spreadsheet as an object and are looking at the rows and columns, there should be such a thing as ActiveSheet.Row.Count (to see the highest used column) or ActiveSheet.Column.Count (to see the highest used row).

You MIGHT need to be careful with this if it happens that the number of rows varies with column or the number of columns varies with row. I.e. if the sheet isn't uniformly populated. (Which it sounds like might be the case.)

 

[Brianwarnock]

I've always used

With ActiveSheet.UsedRange
lastcol = .Cells(1, 1).Column + .Columns.Count - 1
lastrow = .Cells(1, 1).Row + .Rows.Count - 1
End With

without any problems, but I know that many, HaHoBe when he was around, and I think Shades, prefer the Xlup approach, but I don't know why.

Brian

 

[DCrake]

Simple Software Solutions

Thanks all for your input. The issue has now been resolved. I will close this thread.