Plz,

I am trying to use this, but getting error!

I need to get the last active row in the active sheet

Dim LastRow As Integer

LastRow
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSh eet).Cells(Rows.Count
"1").End(xlUp).Row

May be its because of the "1" ???

Thanks

--
Message posted from http://www.ExcelForum.com

I think it is, that should be an integer.

Also, if accessing the active workbook, VBA maintains a pointer to that, so
you don't need to go through the workbooks collection. And similarly with
the active sheet, which also can only be in the active workbook

Try

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


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ajliaks " wrote in message
...
Plz,

I am trying to use this, but getting error!

I need to get the last active row in the active sheet

Dim LastRow As Integer

LastRow =
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSh eet).Cells(Rows.Count,
"1").End(xlUp).Row

May be its because of the "1" ???

Thanks.


---
Message posted from http://www.ExcelForum.com/

I think Bob gave you the answer, but you could use "A", too:

And I think I'd do:

Dim LastRow As Long
Just in case it's a big number (32k).

Dim LastRow As Long
with activesheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
end with

and one mo

workbooks(activeworkbook.name)
is equivalent to
activeworkbook




"ajliaks <" wrote:

Plz,

I am trying to use this, but getting error!

I need to get the last active row in the active sheet

Dim LastRow As Integer

LastRow =
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSh eet).Cells(Rows.Count,
"1").End(xlUp).Row

May be its because of the "1" ???

Thanks.

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

I think you can have an activesheet in every open workbook:

Option Explicit
Sub testme()

Dim wkbk As Workbook
Dim iCtr As Long

For iCtr = 1 To 3
Workbooks.Add 1
ActiveSheet.Name = "hi there" & iCtr
Next iCtr

For Each wkbk In Workbooks
With wkbk.ActiveSheet
MsgBox .Name & "--" & .Parent.Name
End With
Next wkbk

End Sub

Bob Phillips wrote:

I think it is, that should be an integer.

Also, if accessing the active workbook, VBA maintains a pointer to that, so
you don't need to go through the workbooks collection. And similarly with
the active sheet, which also can only be in the active workbook

Try

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

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ajliaks " wrote in message
...
Plz,

I am trying to use this, but getting error!

I need to get the last active row in the active sheet

Dim LastRow As Integer

LastRow =
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSh eet).Cells(Rows.Count,
"1").End(xlUp).Row

May be its because of the "1" ???

Thanks.


---
Message posted from http://www.ExcelForum.com/


--

Dave Peterson

Which is better IMO

Bob

"Dave Peterson" wrote in message
...
I think Bob gave you the answer, but you could use "A", too:

And I think I'd do:

Dim LastRow As Long
Just in case it's a big number (32k).

Dim LastRow As Long
with activesheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
end with

and one mo

workbooks(activeworkbook.name)
is equivalent to
activeworkbook




"ajliaks <" wrote:

Plz,

I am trying to use this, but getting error!

I need to get the last active row in the active sheet

Dim LastRow As Integer

LastRow =
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSh eet).Cells(Rows.Count,
"1").End(xlUp).Row

May be its because of the "1" ???

Thanks.

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Dim lngLastRow As Long

lngLastRow = ActiveSheet.UsedRange.Rows.Count

--
Regards,
Bill


"ajliaks " wrote in message
...
Plz,

I am trying to use this, but getting error!

I need to get the last active row in the active sheet

Dim LastRow As Integer

LastRow =
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSh eet).Cells(Rows.Count,
"1").End(xlUp).Row

May be its because of the "1" ???

Thanks.


---
Message posted from http://www.ExcelForum.com/

That would give the number of rows in the used range--not always the last used
row.

If your data were in A99:A100, you'd get 2.

Maybe

Dim lngLastRow As Long
with activesheet
lngLastRow = .UsedRange.rows(.usedrange.rows.count).row
end with

just in case??


Bill Renaud wrote:

Dim lngLastRow As Long

lngLastRow = ActiveSheet.UsedRange.Rows.Count

--
Regards,
Bill

"ajliaks " wrote in message
...
Plz,

I am trying to use this, but getting error!

I need to get the last active row in the active sheet

Dim LastRow As Integer

LastRow =
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSh eet).Cells(Rows.Count,
"1").End(xlUp).Row

May be its because of the "1" ???

Thanks.


---
Message posted from http://www.ExcelForum.com/


--

Dave Peterson

I agree with you in general. But lots of times, I'll know that there's a column
that has to be filled in (some sort of key).

And if I know the data well enough, I'll use that method.

But if I don't know the data (some generic routine), I'll use the lastcell
(after trying to reset the usedrange).

I don't like the currentregion for the same reason you don't like the
..end(xlup).row. If there's gaps in the columns/rows, you might not get what you
really want.

(I like Debra Dalgleish's approach to find that real last used cell:
http://www.contextures.com/xlfaqApp.html#Unused)

Bill Renaud wrote:

"Dave Peterson" wrote:
<<If your data were in A99:A100, you'd get 2.

True, but then I was assuming that the application was for a list, since
this is where this technique usually shows up. Most people don't leave the
first 98 rows of a worksheet totally blank. (Unless they are some of the
posters to this NG! Grin! I try to teach good design principles rather than
giving the instant answer!)

Most of the other suggestions that were using some variation of
.End(xlUp).Row have the problem that not all columns will have complete
data. Say I have a table 5 columns wide and 100 rows deep (starting in cell
$A$1, of course!). If $A$100 happens to be blank, then any method counting
the number of rows of data only in column $A will return only 99, when the
100th row actually has data (in columns $B:$E). I guess that is why I always
use UsedRange, or CurrentRegion, or some variation of those properties, when
working with a list.
--
Regards,
Bill

--

Dave Peterson

Turns out now that in Excel 2000 SP-3, simply calling the UsedRange property
seems to reset the used range. I don't know how well this routine works in
newer versions. Debra Dalgleish's approach (also seen on the MSDN web site)
used to be what you had to use in previous versions.

Public Sub ResetUsedRange()
ActiveSheet.UsedRange
End Sub

You are 100% correct about the CurrentRegion property returning only
contiguous cells. It must be used carefully. There used to be side effects
with it, which seem to be gone now, and I cannot remember exactly what they
were.
--
Regards,
Bill


"Dave Peterson" wrote in message
...
I agree with you in general. But lots of times, I'll know that there's a
column
that has to be filled in (some sort of key).

And if I know the data well enough, I'll use that method.

But if I don't know the data (some generic routine), I'll use the lastcell
(after trying to reset the usedrange).

I don't like the currentregion for the same reason you don't like the
.end(xlup).row. If there's gaps in the columns/rows, you might not get
what you
really want.

(I like Debra Dalgleish's approach to find that real last used cell:
http://www.contextures.com/xlfaqApp.html#Unused)

Bill Renaud wrote:

"Dave Peterson" wrote:
<<If your data were in A99:A100, you'd get 2.

True, but then I was assuming that the application was for a list, since
this is where this technique usually shows up. Most people don't leave
the
first 98 rows of a worksheet totally blank. (Unless they are some of the
posters to this NG! Grin! I try to teach good design principles rather
than
giving the instant answer!)

Most of the other suggestions that were using some variation of
.End(xlUp).Row have the problem that not all columns will have complete
data. Say I have a table 5 columns wide and 100 rows deep (starting in
cell
$A$1, of course!). If $A$100 happens to be blank, then any method
counting
the number of rows of data only in column $A will return only 99, when
the
100th row actually has data (in columns $B:$E). I guess that is why I
always
use UsedRange, or CurrentRegion, or some variation of those properties,
when
working with a list.
--
Regards,
Bill

--

Dave Peterson

Hi,

Assuming I use one of the methods described above to determine both th
number of rows and number of columns of a spreadsheet how do I go abou
determining the addresses of both cells (to p left and bottom right) i
order to select the range ( to apply formatting etc)

Thanks in advance

Seamu

--
Message posted from http://www.ExcelForum.com

It's okay folks - I worked it out

Sub Last()

Dim lngLastRow As Long
Dim lngLastCol As Long
With ActiveSheet
lngLastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
lngLastCol = .UsedRange.Columns _(.UsedRange.Columns.Count).Column
lastcell = Cells(lngLastRow, lngLastCol).Address
Range("A1", lastcell).Select
End With
End Sub

Thanks anyway

Seamus


---
Message posted from http://www.ExcelForum.com/

Now I remember what the side effects are when using the CurrentRegion
property (verified with a demo program). If you attempt to set a range
variable to a range using the CurrentRegion property, the
Worksheet_SelectionChange event fires (Excel 2000 SP-3). This does not occur
if you use the UsedRange property.
--
Regards,
Bill

It didn't fire in xl2002 SP2.

So there's a reason to upgrade--and colored tabs!



Bill Renaud wrote:

Now I remember what the side effects are when using the CurrentRegion
property (verified with a demo program). If you attempt to set a range
variable to a range using the CurrentRegion property, the
Worksheet_SelectionChange event fires (Excel 2000 SP-3). This does not occur
if you use the UsedRange property.
--
Regards,
Bill

--

Dave Peterson