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

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

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" <(E-Mail Removed)> wrote in message
news:f3pfmi$99e$(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:f3pfmi$99e$(E-Mail Removed)...
>> 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
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:f3pfmi$99e$(E-Mail Removed)...
>> 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
>>
>
>

Looks good! Thanks again.
Fritz

"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:01561B7F-3C15-4377-A7E3-(E-Mail Removed)...
> 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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:f3pfmi$99e$(E-Mail Removed)...
> >> 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
> >>
> >
> >
>
>
>

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