Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question - Type Mismatch
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question - Type Mismatch
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question - Type Mismatch
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question - Type Mismatch
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question - Type Mismatch
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question - Type Mismatch
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question - Type Mismatch
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question - Type Mismatch
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question - Type Mismatch
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question - Type Mismatch
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question - Type Mismatch
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/ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question - Type Mismatch (CurrentRegion property)
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question - Type Mismatch (CurrentRegion property)
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type mismatch... | Excel Discussion (Misc queries) | |||
Type Mismatch | Excel Programming | |||
Type Mismatch | Excel Programming | |||
Type mismatch | Excel Programming | |||
Type Mismatch | Excel Programming |