VBA question - Type Mismatch

A

ajliaks

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(ActiveSheet).Cells(Rows.Count
"1").End(xlUp).Row

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

Thanks
 
B

Bob Phillips

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)
 
D

Dave Peterson

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 more:

workbooks(activeworkbook.name)
is equivalent to
activeworkbook
 
D

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
 
B

Bob Phillips

Which is better IMO

Bob

Dave Peterson said:
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 more:

workbooks(activeworkbook.name)
is equivalent to
activeworkbook
Click to expand...
 
D

Dave Peterson

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??
 
V

Vasant Nanavati

Hi Dave:

I find JE's solution more elegant <g>:

Dim lngLastRow As Long
With ActiveSheet.UsedRange
lngLastRow = .Cells(.Cells.Count).Row
End With

Not to take anything away from your brilliant answer, of course <gd&r>.

Regards,

Vasant.
 
D

Dave Peterson

No offense to JE (and complete offense to you <vbg>):

How about:

Dim lngLastRow As Long
With ActiveSheet.UsedRange
lngLastRow = .Rows(.Rows.Count).Row
End With

I would think that this would be quicker than counting all the cells.





Vasant said:
Hi Dave:

I find JE's solution more elegant <g>:

Dim lngLastRow As Long
With ActiveSheet.UsedRange
lngLastRow = .Cells(.Cells.Count).Row
End With

Not to take anything away from your brilliant answer, of course <gd&r>.

Regards,

Vasant.
Click to expand...
 
V

Vasant Nanavati

Hah ... I guess I asked for that!

--

Vasant

Dave Peterson said:
No offense to JE (and complete offense to you <vbg>):

How about:

Dim lngLastRow As Long
With ActiveSheet.UsedRange
lngLastRow = .Rows(.Rows.Count).Row
End With

I would think that this would be quicker than counting all the cells.
Click to expand...
 
B

Bill Renaud

:
<<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.
 
D

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)
 
B

Bill Renaud

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 said:
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)
Click to expand...
 
S

SOS

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
 
S

SOS

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
 
B

Bill Renaud

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.
 
Top