Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find last row/column with vba?
Is there a way to tell Excel to just go to the end of the populated cells -
either row or column? I have a number of situations where I know the start, but not the end of a range. For example, I need to set an entire worksheet (except the first column) to NumberFormat = "0.0000" I've tried this: xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("B2").End(xlToRight).Num berFormat = "0.0000" but no luck. I also need to insert a formula in a column, but I don't know how many rows it will have. This works: xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("D2:D76").Formula = "=STDEV(E2:AH2)" but I've hardcoded the range. I know it will start at D2, but I don't know that it will always end at D76. I found this code: Private Function LastRow() As Long LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row End Function But I'm not sure if this is the right way to do it. Any suggestions welcome! Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find last row/column with vba?
Function LastRow() As Long
LastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row End Function Function LastCol() As Long LastCol = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column End Function Use like so xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("B2", cells(LastRow, LastCol)).NumberFormat = "0.0000" xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("D2:D" & LastRow).Formula = "=STDEV(E2:AH2)" -- HTH RP (remove nothere from the email address if mailing direct) "deko" wrote in message m... Is there a way to tell Excel to just go to the end of the populated cells - either row or column? I have a number of situations where I know the start, but not the end of a range. For example, I need to set an entire worksheet (except the first column) to NumberFormat = "0.0000" I've tried this: xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("B2").End(xlToRight).Num berFormat = "0.0000" but no luck. I also need to insert a formula in a column, but I don't know how many rows it will have. This works: xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("D2:D76").Formula = "=STDEV(E2:AH2)" but I've hardcoded the range. I know it will start at D2, but I don't know that it will always end at D76. I found this code: Private Function LastRow() As Long LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row End Function But I'm not sure if this is the right way to do it. Any suggestions welcome! Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find last row/column with vba?
Function LastRow() As Long
LastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row End Function Function LastCol() As Long LastCol = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column End Function Use like so xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("B2", cells(LastRow, LastCol)).NumberFormat = "0.0000" xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("D2:D" & LastRow).Formula = "=STDEV(E2:AH2)" Thanks for the reply. But what about SpecialCells? I've been reading about SpecialCells and I'm wondering if that might be quicker. This code is going in a big loop to generate a bunch of worksheets. I suppose I could try each and see. Here'e what (I think) it woudl look like using SpecailCells: lngLastCol = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("A1").SpecialCells(xlCel lTypeLastCell).Column lngLastRow = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("A1").SpecialCells(xlCel lTypeLastCell).Row I tried this like so: xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e(Cells(1, 1), Cells(1, lngLastCol)).EntireColumn.AutoFit but it barfs. In any case, it sounds like the answer is either search for it, or get it with SpecialCells. What do you think about using the "Range(Cells(x,x), Cells(x,x))" syntax? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find last row/column with vba?
Was there something wrong with my suggestion?
Probably lngLastCol = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s.SpecialCells(xlCellTypeL astCell).Column lngLastRow = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s.SpecialCells(xlCellTypeL astCell).Row and With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Range(.Cells(1, 1), .Cells(1,lngLastCol)).EntireColumn.AutoFit End With The With ... End With in the last bit is important -- HTH RP (remove nothere from the email address if mailing direct) "deko" wrote in message ... Function LastRow() As Long LastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row End Function Function LastCol() As Long LastCol = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column End Function Use like so xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("B2", cells(LastRow, LastCol)).NumberFormat = "0.0000" xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("D2:D" & LastRow).Formula = "=STDEV(E2:AH2)" Thanks for the reply. But what about SpecialCells? I've been reading about SpecialCells and I'm wondering if that might be quicker. This code is going in a big loop to generate a bunch of worksheets. I suppose I could try each and see. Here'e what (I think) it woudl look like using SpecailCells: lngLastCol = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("A1").SpecialCells(xlCel lTypeLastCell).Column lngLastRow = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("A1").SpecialCells(xlCel lTypeLastCell).Row I tried this like so: xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e(Cells(1, 1), Cells(1, lngLastCol)).EntireColumn.AutoFit but it barfs. In any case, it sounds like the answer is either search for it, or get it with SpecialCells. What do you think about using the "Range(Cells(x,x), Cells(x,x))" syntax? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find last row/column with vba?
For the OP :
Don't use SpecialCells(xlCellTypeLastCell) It will not always give you the right answer. If you deleteor clear a few rows/cells it will not update. You must save the file first -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Was there something wrong with my suggestion? Probably lngLastCol = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s.SpecialCells(xlCellTypeL astCell).Column lngLastRow = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s.SpecialCells(xlCellTypeL astCell).Row and With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Range(.Cells(1, 1), .Cells(1,lngLastCol)).EntireColumn.AutoFit End With The With ... End With in the last bit is important -- HTH RP (remove nothere from the email address if mailing direct) "deko" wrote in message ... Function LastRow() As Long LastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row End Function Function LastCol() As Long LastCol = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column End Function Use like so xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("B2", cells(LastRow, LastCol)).NumberFormat = "0.0000" xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("D2:D" & LastRow).Formula = "=STDEV(E2:AH2)" Thanks for the reply. But what about SpecialCells? I've been reading about SpecialCells and I'm wondering if that might be quicker. This code is going in a big loop to generate a bunch of worksheets. I suppose I could try each and see. Here'e what (I think) it woudl look like using SpecailCells: lngLastCol = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("A1").SpecialCells(xlCel lTypeLastCell).Column lngLastRow = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("A1").SpecialCells(xlCel lTypeLastCell).Row I tried this like so: xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e(Cells(1, 1), Cells(1, lngLastCol)).EntireColumn.AutoFit but it barfs. In any case, it sounds like the answer is either search for it, or get it with SpecialCells. What do you think about using the "Range(Cells(x,x), Cells(x,x))" syntax? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find last row/column with vba?
Was there something wrong with my suggestion?
Not at all. I was just wondering about speed compared to the built-in SpecialCells function. As I mentioned, my code is executed within a loop that creates a bunch (50 to 100) worksheets. Thanks for the help! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find last row/column with vba?
Thanks Ron. I thought about pointing that out, but (very badly) couldn't be
bothered as the OP ignored my suggestion. Getting petulant in my old age :-) Bob "Ron de Bruin" wrote in message ... For the OP : Don't use SpecialCells(xlCellTypeLastCell) It will not always give you the right answer. If you deleteor clear a few rows/cells it will not update. You must save the file first -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Was there something wrong with my suggestion? Probably lngLastCol = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s.SpecialCells(xlCellTypeL astCell).Column lngLastRow = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s.SpecialCells(xlCellTypeL astCell).Row and With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Range(.Cells(1, 1), .Cells(1,lngLastCol)).EntireColumn.AutoFit End With The With ... End With in the last bit is important -- HTH RP (remove nothere from the email address if mailing direct) "deko" wrote in message ... Function LastRow() As Long LastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row End Function Function LastCol() As Long LastCol = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column End Function Use like so xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("B2", cells(LastRow, LastCol)).NumberFormat = "0.0000" xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("D2:D" & LastRow).Formula = "=STDEV(E2:AH2)" Thanks for the reply. But what about SpecialCells? I've been reading about SpecialCells and I'm wondering if that might be quicker. This code is going in a big loop to generate a bunch of worksheets. I suppose I could try each and see. Here'e what (I think) it woudl look like using SpecailCells: lngLastCol = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("A1").SpecialCells(xlCel lTypeLastCell).Column lngLastRow = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("A1").SpecialCells(xlCel lTypeLastCell).Row I tried this like so: xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e(Cells(1, 1), Cells(1, lngLastCol)).EntireColumn.AutoFit but it barfs. In any case, it sounds like the answer is either search for it, or get it with SpecialCells. What do you think about using the "Range(Cells(x,x), Cells(x,x))" syntax? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find last row/column with vba?
It is not looping so it would be fast. Probably not as fast as SpecialCells,
but as Ron points out, SpecialCells will give a wrong answer in certain circumstances. -- HTH RP (remove nothere from the email address if mailing direct) "deko" wrote in message m... Was there something wrong with my suggestion? Not at all. I was just wondering about speed compared to the built-in SpecialCells function. As I mentioned, my code is executed within a loop that creates a bunch (50 to 100) worksheets. Thanks for the help! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find last row/column with vba?
For the OP :
Don't use SpecialCells(xlCellTypeLastCell) It will not always give you the right answer. If you deleteor clear a few rows/cells it will not update. You must save the file first I see. However, in this situation - where I'm creating a new worksheet on the fly, inserting data and saving it - there's no chance of cells being deleted or cleared. And once I spit out the 80 or so worksheets, I'm done. It's the latency involved with searching each worksheet for the last cell that I was thinking about. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find last row/column with vba?
Hi!
The following function is very useful and covers a wide spectrum of situations (whole sheets, individual rows or cols) Public Function GetLast(Optional BookName As String, Optional SheetName As String, Optional Column As Boolean, Optional ColOrRow As String) As Integer ' GetLast Class method by Mark Bigelow ' mjbigelow at hotmail dot com Dim objFind As Range If BookName = "" Then BookName = ActiveWorkbook.Name End If If SheetName = "" Then SheetName = Workbooks(BookName).ActiveSheet.Name End If On Error Resume Next If Column = True Then If ColOrRow = "" Then Set objFind = Workbooks(BookName).Sheets(SheetName).UsedRange Else Set objFind = Workbooks(BookName).Sheets(SheetName).Range(ColOrR ow & ":" & ColOrRow) End If GetLast = objFind.Find(What:="*", searchdirection:=xlPrevious, searchOrder:=xlByColumns, LookIn:=xlValues).Column If Err.Number < 0 Then GetLast = 1 Exit Function End If Else If ColOrRow = "" Then Set objFind = Workbooks(BookName).Sheets(SheetName).UsedRange Else Set objFind = Workbooks(BookName).Sheets(SheetName).Range(ColOrR ow & ":" & ColOrRow) End If GetLast = objFind.Find(What:="*", searchdirection:=xlPrevious, searchOrder:=xlByRows, LookIn:=xlValues).Row If Err.Number < 0 Then GetLast = 1 Exit Function End If End If On Error GoTo 0 End Function Call the function with r=GetLast (for last row in sheet) or r=GetLast( , , , "A") for last row in col A or c=GetLast( , , True, "15") for last column in row 15. etc Alf |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find last row/column with vba?
It is not looping so it would be fast. Probably not as fast as
SpecialCells, but as Ron points out, SpecialCells will give a wrong answer in certain circumstances. I will definitely use those functions - from what I've read they are preferred over SpecialCells for the reasons Ron described. Still, it's using the cell number to define the last column that's got me confused. For example, how do I set the NumberFormat below? Here is my loop: For i = 1 To sn.Count With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Cells(1).EntireRow.HorizontalAlignment = xlCenter .Cells(1).EntireRow.Font.Bold = True .Range("C2:" & LastColumn & LastRow).Cells.NumberFormat = "0.0000" .Range("D2:D" & LastRow).Formula = "=STDEV(E2:AH2)" .Columns.AutoFit End With Next i Clearly, "LastColumn & LastRow" fails. But how else do I define this range? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find last row/column with vba?
Hi!
The following function is very useful and covers a wide spectrum of situations (whole sheets, individual rows or cols) Public Function GetLast(Optional BookName As String, Optional SheetName As String, Optional Column As Boolean, Optional ColOrRow As String) As Integer ' GetLast Class method by Mark Bigelow ' mjbigelow at hotmail dot com Dim objFind As Range If BookName = "" Then BookName = ActiveWorkbook.Name End If If SheetName = "" Then SheetName = Workbooks(BookName).ActiveSheet.Name End If On Error Resume Next If Column = True Then If ColOrRow = "" Then Set objFind = Workbooks(BookName).Sheets(SheetName).UsedRange Else Set objFind = Workbooks(BookName).Sheets(SheetName).Range(ColOrR ow & ":" & ColOrRow) End If GetLast = objFind.Find(What:="*", searchdirection:=xlPrevious, searchOrder:=xlByColumns, LookIn:=xlValues).Column If Err.Number < 0 Then GetLast = 1 Exit Function End If Else If ColOrRow = "" Then Set objFind = Workbooks(BookName).Sheets(SheetName).UsedRange Else Set objFind = Workbooks(BookName).Sheets(SheetName).Range(ColOrR ow & ":" & ColOrRow) End If GetLast = objFind.Find(What:="*", searchdirection:=xlPrevious, searchOrder:=xlByRows, LookIn:=xlValues).Row If Err.Number < 0 Then GetLast = 1 Exit Function End If End If On Error GoTo 0 End Function Call the function with r=GetLast (for last row in sheet) or r=GetLast( , , , "A") for last row in col A or c=GetLast( , , True, "15") for last column in row 15. Result r is a (long) number which you can then use. Alf (Apologies if this is a double post: the system is playing tricks at present) |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find last row/column with vba?
They are both Longs, so you can't use like that, as it is expecting
string/long. Try this For i = 1 To sn.Count With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Cells(1).EntireRow.HorizontalAlignment = xlCenter .Cells(1).EntireRow.Font.Bold = True .Range(.Range("C2") & .Cells(LastRow, LastColumn)).NumberFormat = "0.0000" .Range("D2:D" & LastRow).Formula = "=STDEV(E2:AH2)" .Columns.AutoFit End With Next i -- HTH RP (remove nothere from the email address if mailing direct) "deko" wrote in message m... It is not looping so it would be fast. Probably not as fast as SpecialCells, but as Ron points out, SpecialCells will give a wrong answer in certain circumstances. I will definitely use those functions - from what I've read they are preferred over SpecialCells for the reasons Ron described. Still, it's using the cell number to define the last column that's got me confused. For example, how do I set the NumberFormat below? Here is my loop: For i = 1 To sn.Count With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .Cells(1).EntireRow.HorizontalAlignment = xlCenter .Cells(1).EntireRow.Font.Bold = True .Range("C2:" & LastColumn & LastRow).Cells.NumberFormat = "0.0000" .Range("D2:D" & LastRow).Formula = "=STDEV(E2:AH2)" .Columns.AutoFit End With Next i Clearly, "LastColumn & LastRow" fails. But how else do I define this range? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FInd common data in one column then add number in adjacent column | Excel Worksheet Functions | |||
find last row value in column when using MATCH to find column | Excel Worksheet Functions | |||
Find something in column a then find if column B matches criteria | Excel Discussion (Misc queries) | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Need Macro to Find Column Heading -- if none, then insert new column | Excel Programming |