Determine sheet dimensions

[Fritz Hilgemann]

Hello NG,
I rarely use Excel programming which doesn't mak me an expert. So I hope to
find an answer on this:
From a single sheet, I want to determine it's used dimension, that the last
non-empty row (over all columns) and the last non-empty column (over all
rows). The sheet, though, may have empty cells, rows or columns in between.
I do not want to iterate and test 16 Mio. cells separately, I am hoping on a
method or attribute I have overlooked or a tricky algorithm.
Regards
Fritz

 

[Guest]

Give this a try... Note that it returns a range object (cell)

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function

Use it like this

sub test
dim rng as range

msgbox lastcell(Sheets("Sheet1")).address
set rng = lastcell
rng.select
end sub

 

[Roger Govier]

Hi Fritz

Maybe this will help you

Sub FindLastCell()
Dim lastrow As Long, lastcolumn As Long, lastcell As Variant, Myarea As
Variant
lastrow = Cells(Rows.Count, 1).End(xlUp).row
MsgBox "Last ROW Number is " & lastrow, , ""
lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox "Last COLUMN Number is " & lastcolumn, , ""
lastcell = Cells(lastrow, lastcolumn).Address
MsgBox "Last Cell Address is " & lastcell, , ""
Myarea = ActiveSheet.UsedRange.Address
MsgBox "Used Range is " & Myarea, , ""
End Sub

 

[Fritz Hilgemann]

Thanks Roger and Jim. I'll try it out, but it'll take some time since I need
to translate this to VC++.
Regards
Fritz

 

[Fritz Hilgemann]

Hi Roger,

unfortunately, this method only returns the last entry of a single row or
column. To find out the full dimension, I would have to test 65535 rows plus
256 cols (which indeed is less than 65535 * 256 cells, but still requires
lots of CPU).

Regards
Fritz

 

[Fritz Hilgemann]

Looks good! Thanks again.
Fritz

Give this a try... Note that it returns a range object (cell)

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function

Use it like this

sub test
dim rng as range

msgbox lastcell(Sheets("Sheet1")).address
set rng = lastcell
rng.select
end sub

 

[Guest]

To meet your requirements Rogers method would require you to loop thorugh the
256 columns (so not too intensive at all). Starting at colum IV looping
backwards through the columns using xlUp to determine the row number. By
storing the first column where the row number is greater than 0 and the
largest row number of the 256 columns you can determine the max row and
column number... I still prefer my method but to each his own. When we hit
xl2007 with 16k columns my method (stolen from someone else but I am not too
sure whom) is by far better...

 

[Guest]

There is a property usedrange in excel

try: msgbox Sheet1.UsedRange.Address

and a messagebox will return for example $B$4:$G$16