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

Selection.End(xlDown).Row

and

Selection.End(xlToRight).Column

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Shinya Koizumi" wrote in message
...
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

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

Dim app as Excel.Application

app.ActiveSheet.Rows.Count

worked for me

thanks

Shinya

Doesn't that just return 65536 every time?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Shinya Koizumi" wrote in message
...
Dim app as Excel.Application

app.ActiveSheet.Rows.Count

worked for me

thanks

Shinya