help with number of columns on sheet

C

Chris Barnett

hello there

i was kindly given some code for finding the last used row on a worksheet as
below

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row


i would be grateful for some help in finding a way of identifying the last
used column

LastColumn = ?

thanks
Chris Barnett
 
D

Dave Peterson

That actually finds the last used row in column A.

Dim LastRow as long
dim LastColumn as long

with activesheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastColumn = .cells(1,.columns.count).end(xltoleft).column
end with

This uses Row 1 to find the last used column.
 
B

Bob Phillips

The best way IMO to get last row and last column is with these tow
functions. Best way because each row may have a different number of columns
and vice versa, and these functions don't depend on which row/column to
search for the last

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



--

HTH

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

Harlan Grove

Bob Phillips said:
The best way IMO to get last row and last column is with these tow
functions. Best way because each row may have a different number of columns
and vice versa, and these functions don't depend on which row/column to
search for the last

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
Click to expand...

A wee problem being that .Find won't run in udfs under some older versions
of Excel (certainly not under XL97, and I don't recall whether it works or
not under XL2K).

If udfs are needed that run under XL97, then there's no avoiding iterating
through columns or rows.
 
C

Chris Barnett

just thought i'd say thanks for your efforts.
the simple method will be fine for what i need but am still on a steep
learning curve for VB (been a while since i did any programming - C64 days)
so it's always good to get other ways round things so thanks for the more
versatile answers as well.

cheers
chris barnett
 
Top