Excel 2010 - Find last used cell in range

[Heidestrand]

Hello,
I need a little help very quick, I couldn't solve this problem on my own:
I want to find the last written cell in the range A2:C700 using VBA and store the line number in a variable.
I already have something like this but it's only for one column and not a range:

lastcell = Sheets("worktable").Cells(Rows.Count, 1).End(xlUp).Row

I would appreciate any help

 

[Rx_]

By last do you mean by highest Collumn/Row combo in the range?
Would the column or row entry be the last?
1,2,3,4,5
3,5,6
3

The CurrentRegion will pick the best rectangular area of any data set.
Put the start point at the 1. Current Region would return 3 Rows, 5 Columns.
Knowing the Row number, would allow for the next step.
But, can your Range be
1,2,3,4,5
3,5,6,,,,,,,,12
3

Between 6 and 12, the CurrentRegion would exclude 12.
Would 5 be the last or would 3?

OR: The Last entry anywhere in the range by datetime entry?

A littl more information would be helpful.

 

[Heidestrand]

@Rx_:
Thank you for your answer and your help

This is a basic table I want to apply my code on:

As you can see in C9 there is one cell with a '1' written in it.
So this is the last used cell in my range A to C. And I want to find
the line, in this case line 9.

So he should go down in each column in my range A:C
until it finds the last row where a cell is used.

Do you understand me?

 

[namliam]

Something like this?

Dim Lastrow as integer
Range("A1").select
activecell.currentregion.select

Lastrow = selection.rows.count

msgbox "Last row used: " & lastrow

Note this returns the last ROW, not the last cell, it will not differentiate between a, b or c

P.S. above is complete untested aircode, I hope it works *fingers crossed*

 

[Brianwarnock]

Namliam's code only finds the last row with data in column A if my memory is correct, it been a long time. A Google search does reveal many hits, this one also lists the pros and cons.

http://www.rondebruin.nl/win/s9/win005.htm

Brian

Apologies to Namliam I didn't read his code carefully enough, but doesn't the use of Currentregion have problems if there are imbedded blank rows?

 

[Heidestrand]

@namliam:

Thank you for your code! It works well so far. I'm just curious.. if you say Range("A1").select, how does he know he should use the range from A to C?
I ask because you said it will not differentiate between A,B and C, but somehow.. it returns the row number when I write something in C.
In my case I want specifically address the range A1 to C.. open end

 

[Brianwarnock]

I've also seen this

LastRow = sht.cells.Find("*",searchorder:=xlbyrows,searchdirection:=xlPrevious).Row

Where sht is the reference to the worksheet, I'm sure that it can be modified for a range but have not done it.

Brian

 

[Heidestrand]

@Brianwarnock:
Your solutions also works, I just tested it. But the looks for the last used cell in the whole worksheet and not in the special are A to C

 

[Brianwarnock]

I did say that, and also that I thought that it could be modified, try replacing sht by

Sheets("yoursheetname").Range("A:C")
or ("A1:C9") or whatever

BTW it is normal to Dim lastrow as Long

Brian

 

[Heidestrand]

@Brianwarnock: I know you did say that And it works like a charm now, so everything is good.

Thanks a lot, to all of you =)

 

[namliam]

Apologies to Namliam I didn't read his code carefully enough, but doesn't the use of Currentregion have problems if there are imbedded blank rows?

It does have a problem with blank rows, as does it with black columns...
However, "proper" tables in 99.995% of all cases will not have either.

Yes, as long, instead of as integer, like I said, fast on forum typing without much thought to it....

Currentregion selects the table it is currently in, thus by starting at A1, it finds the table to the right and to the bottom... which in your case might give you a problem trying to ignore D and beyond unless there is an empty column to seperate the two.