Last used cell in column

[mp]

Is there a better way to do this?

Function LastRowOfData(oWs As Worksheet, Column As String) As Long
Dim currWs As Worksheet
Set currWs = ActiveSheet

oWs.Activate
Range(Column & "65536").End(xlUp).Select
Dim oCell As Range
Set oCell = ActiveCell
LastRowOfData = oCell.row

currWs.Activate

End Function

it works but looks like a terrible way to find that info to me?

thanks
mark

 

[Clif McIrvin]

Is there a better way to do this?

Function LastRowOfData(oWs As Worksheet, Column As String) As Long
Dim currWs As Worksheet
Set currWs = ActiveSheet

oWs.Activate
Range(Column & "65536").End(xlUp).Select
Dim oCell As Range
Set oCell = ActiveCell
LastRowOfData = oCell.row

currWs.Activate

End Function

it works but looks like a terrible way to find that info to me?

thanks
mark

I've seen others suggest using .End(xlUp) for quickly finding the last
populated cell in a column, so I think that is fine. Your use of
..Select and .Activate is slowing the process down without reason,
however. It took me a long time to realize what was happening; and I
don't know if the explanation is in the help files or not ... but
because the macro recorder is driven off the user interface (i.e., cells
and worksheets are getting selected and activated) that's the way the
recorder generates code. This will run much faster:

Function LastRowOfData(oWs As Worksheet, Column As String) As Long

Set LastRowOfData = oWs.Range(Column & "65536").End(xlUp).Row

End Function

 

[Clif McIrvin]

I've seen others suggest using .End(xlUp) for quickly finding the last
populated cell in a column, so I think that is fine. Your use of
.Select and .Activate is slowing the process down without reason,
however. It took me a long time to realize what was happening; and I
don't know if the explanation is in the help files or not ... but
because the macro recorder is driven off the user interface (i.e.,
cells and worksheets are getting selected and activated) that's the
way the recorder generates code. This will run much faster:

Function LastRowOfData(oWs As Worksheet, Column As String) As Long

Set LastRowOfData = oWs.Range(Column & "65536").End(xlUp).Row

End Function

For xl2007 and beyond, you need to use 1048576 for the last possible
row.

 

[mp]

I've seen others suggest using .End(xlUp) for quickly finding the last
populated cell in a column, so I think that is fine. Your use of .Select
and .Activate is slowing the process down without reason, however. It
took me a long time to realize what was happening; and I don't know if the
explanation is in the help files or not ... but because the macro recorder
is driven off the user interface (i.e., cells and worksheets are getting
selected and activated) that's the way the recorder generates code. This
will run much faster:

Function LastRowOfData(oWs As Worksheet, Column As String) As Long

Set LastRowOfData = oWs.Range(Column & "65536").End(xlUp).Row

End Function

Many thanks.
I felt the select and activate were wrong, but had found a similar usage as
a basis for this function of mine.
wasn't sure how to get rid of them.
Thanks
mark

 

[GS]

Clif McIrvin formulated the question :

For xl2007 and beyond, you need to use 1048576 for the last possible row.

Actually, you could use this to avoid having to hard-code the number of
rows.

LastRowOfData = oWs.Cells(oWs.Rows.Count, _
Columns(Column).Column).End(xlUp).Row

If your function accepted Column As Long instead of a string, it could
be done like this:

LastRowOfData = oWs.Cells(oWs.Rows.Count,
Columns(Column).End(xlUp).Row

 

[GS]

GS was thinking very hard :

LastRowOfData = oWs.Cells(oWs.Rows.Count, Columns(Column).End(xlUp).Row

Oops! Change to this:

LastRowOfData = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).Row

 

[mp]

GS was thinking very hard :

Oops! Change to this:

LastRowOfData = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).Row

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

nice addition, thanks
mark

 

[Javed]

Just another way to do it

cells.specialcells(xlcelltypelastcell).row

this will return the last used cell's row number.The other process can
get the last row if data is there but if you want to know last
accessed row (where some formatting done) you have to use that
statement.Because it can track the formatted cells as accessed cell.

 

[mp]

Just another way to do it

cells.specialcells(xlcelltypelastcell).row

this will return the last used cell's row number.The other process can
get the last row if data is there but if you want to know last
accessed row (where some formatting done) you have to use that
statement.Because it can track the formatted cells as accessed cell.

Good to know, thanks
But how do I tell it which column to consider?
Thanks
Mark

 

[Javed]

Sorry MP.It is worksheet wide setting.not column based.
I posted it just for littl addition.

 

[Rick Rothstein]

Just another way to do it

cells.specialcells(xlcelltypelastcell).row

this will return the last used cell's row number.

Actually, that is not a good way to find the last row as SpecialCells can be
fooled. Try this experiment. Go to a new sheet that never had any entries
made in it (insert a new sheet would be best), then type an X in B3. Next
type an X in B15, then select B15 and press the Delete key on the keyboard.
Now go into the VB editor and execute your line of code in the Immediate
Window like this...

? cells.specialcells(xlcelltypelastcell).row

It should print out 3, but I am betting it printed out 15 instead. There are
ways to reset the last cell as SpecialCells sees it, but it isn't worth the
effort. By the way, the last row will reset to the correct value next time
the workbook is opened, but the problem is you cannot know if your user will
make an accidental entry (like the X in B15 I had you do) and then,
realizing it, delete it using the Delete key.

Rick Rothstein (MVP - Excel)

 

[Jim Cone]

Rick,
In addition, I have had SpecialCells(xlCellTypeLastCell) return multiple cells.
I don't use it at all anymore.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Formats & Styles: lists or removes unused styles & number formats - in the free folder)





"Rick Rothstein"
<[email protected]>
wrote in message

 

[Rick Rothstein]

In addition, I have had SpecialCells(xlCellTypeLastCell) return multiple

cells.

Do you mean it returned a range consisting of multiple cells for a single
call to SpecialCells? Or did you mean it returned one cell the first time
you called it and a different cell the next time it was called?

Rick Rothstein (MVP - Excel)

 

[mp]

GS was thinking very hard :

Oops! Change to this:

LastRowOfData = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).Row

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I thought that was working for me last night,
now it's returning the row past the last row with data
I have data in first 10 rows, row 11 is blank
this is returning 11 instead of 10
am I doing something wrong?

I have closed and reopened sheet(in case I had put something in 11 and
deleted(which i don't think i did) )

Function LastRowOfData(oWs As Worksheet, Column As Long) As Long

Dim lastRow As Long
lastRow = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).row
Debug.Print "last row of data = " & lastRow

LastRowOfData = lastRow
End Function

 

[mp]

I thought that was working for me last night,
now it's returning the row past the last row with data
I have data in first 10 rows, row 11 is blank
this is returning 11 instead of 10
am I doing something wrong?

I have closed and reopened sheet(in case I had put something in 11 and
deleted(which i don't think i did) )

Function LastRowOfData(oWs As Worksheet, Column As Long) As Long

Dim lastRow As Long
lastRow = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).row
Debug.Print "last row of data = " & lastRow

LastRowOfData = lastRow
End Function

ok, it gets even weirder...
I put somethign in row 11 and reran the test...
it worked...returned 11 now....
then I deleted the data in 11 and reran...
it works again, now it returned 10
???? wtf ????
oh well,
thanks anyway...

does this indicate it's a buggy way to do this?
thanks
mark

 

[Jim Cone]

Yes, "it returned a range consisting of multiple cells for a single call"

I believe I was looking for the last column and LastCell was returning a reference to two
or three cells from the same row.
This was in August 2010 - I did not keep detailed notes, just a message to myself about
don't do that.
I suspect it is a rare occurrence and something could have contributed to it, but I don't
know what.
The UsedRange seems to be a reliable method for finding a general starting area.
'---
Jim Cone




"Rick Rothstein" <[email protected]>
wrote in message

 

[Rick Rothstein]

Yes, "it returned a range consisting of multiple cells for a single call"

I believe I was looking for the last column and LastCell was returning a
reference to two or three cells from the same row.

That is a new one for me, but I do not doubt you at all... SpecialCells can
be flakey.

This was in August 2010 - I did not keep detailed notes, just a message
to myself about don't do that.

LOL... yep, that is a good note to leave for yourself.

The UsedRange seems to be a reliable method for finding a general starting
area.

Yes, it seems to track that pretty well, but that ability is almost never
needed... most of the time Row 1 is used for a header or starting value and,
when it isn't, the user knows that starting row that should be used and it
is easy to just set a constant (Const statement) to it, that way one can use
the more self-documenting name instead.

Rick Rothstein (MVP - Excel)

 

[GS]

mp used his keyboard to write :

ok, it gets even weirder...
I put somethign in row 11 and reran the test...
it worked...returned 11 now....
then I deleted the data in 11 and reran...
it works again, now it returned 10
???? wtf ????
oh well,
thanks anyway...

does this indicate it's a buggy way to do this?
thanks
mark

I suspect that row 11 had a space character in it. Some people have the
very nasty habit to use that for clearing a cell's contents. So, if it
returned 11 then that cell was not empty!

 

[mp]

mp used his keyboard to write : []

does this indicate it's a buggy way to do this?
thanks
mark

I suspect that row 11 had a space character in it. Some people have the
very nasty habit to use that for clearing a cell's contents. So, if it
returned 11 then that cell was not empty!

ha, that is entirely possible,
thanks, i never would have thought of that.
mark

 

[mp]

mp used his keyboard to write :

news:[email protected]...

[]

I suspect that row 11 had a space character in it. Some people have the
very nasty habit to use that for clearing a cell's contents. So, if it
returned 11 then that cell was not empty!

ha, that is entirely possible,
thanks, i never would have thought of that.
mark

now that i have re-run the code that fills those ranges,
I see it does put a space in the row after the actual data
I have no idea why it would do that...
but till i get that worked out...
is this a terrible way to workaround the problem of
blank cells that aren't really blank ?

Function LastRowOfData(oWs As Worksheet, Column As Long) As Long
'get rid of spaces in seemingly blank cells
Dim oRng As Range
For Each oRng In oWs.UsedRange
If Len(Trim(oRng.Value)) = 0 Then
oRng.Value = ""
End If
Next oRng

LastRowOfData = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).row

End Function