max row and max col

[Shinya Koizumi]

How to find out the maximum row and col in the current worksheet without
going to the actual cell?

I know if i actually go to the last cell i can find out.

Selection.End(xlToRight).Select

[Bob Phillips]

Selection.End(xlDown).Row

and

Selection.End(xlToRight).Column

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Shinya Koizumi" <[email protected]> wrote in message
news:#[email protected]...
> How to find out the maximum row and col in the current worksheet without
> going to the actual cell?
>
> I know if i actually go to the last cell i can find out.
>
> Selection.End(xlToRight).Select
>
>
>
>

[Jim Thomlinson]

This should work for you. It finds the right most populated column and bottom
most populeted row.

Public Sub LastCell(Optional ByVal wks As Worksheet)
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
End Sub

--
HTH...

Jim Thomlinson


"Shinya Koizumi" wrote:

> How to find out the maximum row and col in the current worksheet without
> going to the actual cell?
>
> I know if i actually go to the last cell i can find out.
>
> Selection.End(xlToRight).Select
>
>
>
>
>

[Shinya Koizumi]

Dim app as Excel.Application

app.ActiveSheet.Rows.Count

worked for me

thanks

Shinya

[Bob Phillips]

Doesn't that just return 65536 every time?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Shinya Koizumi" <[email protected]> wrote in message
news:[email protected]...
> Dim app as Excel.Application
>
> app.ActiveSheet.Rows.Count
>
> worked for me
>
> thanks
>
> Shinya
>
>