How to find last row/column with vba?

[deko]

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)).Range("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)).Range("D276").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.

 

[Bob Phillips]

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)).Range("B2", cells(LastRow,
LastCol)).NumberFormat = "0.0000"

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("D2" &
LastRow).Formula = "=STDEV(E2:AH2)"

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[deko]

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)).Range("B2", cells(LastRow,
LastCol)).NumberFormat = "0.0000"

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("D2" &
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)).Range("A1").SpecialCells(xlCel
lTypeLastCell).Column
lngLastRow =
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("A1").SpecialCells(xlCel
lTypeLastCell).Row

I tried this like so:

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range(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?

 

[Bob Phillips]

Was there something wrong with my suggestion?

Probably

lngLastCol =
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells.SpecialCells(xlCellTypeL
astCell).Column
lngLastRow =
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells.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)

 

[Ron de Bruin]

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

 

[deko]

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!

 

[Bob Phillips]

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

 

[Bob Phillips]

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]

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.

 

[AlfD]

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

 

[deko]

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" & LastRow).Formula = "=STDEV(E2:AH2)"
.Columns.AutoFit
End With
Next i

Clearly, "LastColumn & LastRow" fails. But how else do I define this range?

 

[AlfD]

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)

 

[Bob Phillips]

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" & LastRow).Formula = "=STDEV(E2:AH2)"
.Columns.AutoFit
End With
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)