Thanks Rick for you help it's working !!!!
"Rick Rothstein (MVP - VB)" wrote in
message ...
Give this function a try...

Function MaxRowInUse(Optional WS As Worksheet, Optional _
FactorInHiddenRows As Boolean = False) As Long
Dim X As Long
Dim LastRow As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Columns.Count
If Not (Not FactorInHiddenRows And Columns(X).Hidden) Then
LastRow = .Cells(.Rows.Count, X).End(xlUp).Row
If LastRow MaxRowInUse Then MaxRowInUse = LastRow
End If
Next
End With
End Function

Note: If you don't specify a worksheet in the first (optional) argument,
then the active sheet is used. The second optional argument is the
interesting one... it lets you determine whether to include hidden rows
when determining the maximum row that is in use; that is, if a hidden row
contains the maximum row, it will be ignored unless the second argument is
set to True. This allows you to get the maximum row for what you see on
the worksheet rather than for what any hidden data would return. I wasn't
sure which would be the most logical default for this second argument, so
I chose not factor in hidden rows (that is, the functions return the
maximum row for only the visible data); if desired, this can be easily
changed in the declaration headers for the function (change the False to
True).

Rick


"Jac" wrote in message
...
I want to retreive the last row

Thanks !

"Rick Rothstein (MVP - VB)" wrote
in message ...
The last cell where? Do you mean the last row containing data? Or the
last column containing data? Or something else? If something else, then
you have to tell us which is the last "cell" between, say, B4 and C3
(assuming they were the only cells filled in).

Rick


"Jac" wrote in message
...
Tom or any other one :)

How can i find the last cell in a sheet that has somethin written in it
?

Thanks !

"Tom Hutchins" wrote in message
...
Try

Range("A" & cpt & ":B" & cpt + 20).Select

You have to put the variable outside the set of double quotes, or
Excel
thinks it is part of the string. Same with the +20.

Hope this helps,

Hutch

"Jac" wrote:

Hi i want to select a range of cell with

Sheets("test").Select
Range("A1:B21").Select

Instead of using A1 and B21 i would like to use a variable that would
start
at 1

Dim cpt as integer
cpt = 1
Range("A & cpt:B & cpt +20")

I dont know how to write this

Can someone help me

Thanks