Determine sheet dimensions
 

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

Determine sheet dimensions
 

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
--
HTH...

Jim Thomlinson


"Fritz Hilgemann" wrote:

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

Determine sheet dimensions
 

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
--
Regards

Roger Govier


"Fritz Hilgemann" wrote in message
...
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

Determine sheet dimensions
 

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

"Roger Govier" wrote in message
...
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
--
Regards

Roger Govier


"Fritz Hilgemann" wrote in message
...
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

Determine sheet dimensions
 

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

"Roger Govier" wrote in message
...
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
--
Regards

Roger Govier


"Fritz Hilgemann" wrote in message
...
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

Determine sheet dimensions
 

Looks good! Thanks again.
Fritz

"Jim Thomlinson" wrote in message
...
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
--
HTH...

Jim Thomlinson


"Fritz Hilgemann" wrote:

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

Determine sheet dimensions
 

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...
--
HTH...

Jim Thomlinson


"Fritz Hilgemann" wrote:

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

"Roger Govier" wrote in message
...
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
--
Regards

Roger Govier


"Fritz Hilgemann" wrote in message
...
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

Determine sheet dimensions
 

There is a property usedrange in excel

try: msgbox Sheet1.UsedRange.Address

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

"Fritz Hilgemann" wrote:

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