Thank you guys.

I also found 2 solutions:

#1
Worksheets(2).Range("A65536").End(xlUp).Row

#2
Function GetLastUsedCell(ColNum As Long) As Long

Const BottomRowNum = 65536

Dim LastUsedCell As Long
Dim UsedCellCount As Long
Dim LowerCellRange As Range
Dim CurrentCell As Range

' Ensure function recalculates each time worksheet is updated

Application.Volatile
With ActiveSheet

' Check that bottom cell is not empty

If Not IsEmpty(.Cells(BottomRowNum, ColNum)) Then
GetLastUsedCell = BottomRowNum
Exit Function
End If

' Estimate position of last used cell

LastUsedCell = .Cells(BottomRowNum, ColNum).End(xlUp).Row
If LastUsedCell = 1 Then

' Check cell as it may be empty. If so, return 0.

If IsEmpty(.Cells(1, ColNum)) Then
GetLastUsedCell = 0
Exit Function
End If
End If

Set LowerCellRange = Intersect(.UsedRange, _
.Range(.Cells(LastUsedCell + 1, _
ColNum), .Cells(BottomRowNum, ColNum)))

If Not LowerCellRange Is Nothing Then

' Check for hidden non-empty cells

UsedCellCount = _
Application.WorksheetFunction.CountA(LowerCellRang e)
If UsedCellCount 0 Then
Set CurrentCell = .Cells(LastUsedCell + _
LowerCellRange.Rows.Count, ColNum)

' Check vertically from bottom (of LowerCellRange)
' until first hidden non-empty cell is found

While IsEmpty(CurrentCell)
Set CurrentCell = CurrentCell.Offset(-1, 0)
Wend
LastUsedCell = CurrentCell.Row
Set CurrentCell = Nothing
End If
Set LowerCellRange = Nothing
End If
End With

GetLastUsedCell = LastUsedCell
End Function

"Rick Rothstein (MVP - VB)" wrote:

This function should do that for you...

Function FirstEmptyRow(Col As Variant) As Long
Dim Blanks As Range
If Cells(1, Col).Formula = "" Then
FirstEmptyRow = 1
Else
On Error GoTo NoInternalBlanks
FirstEmptyRow = Columns(Col).SpecialCells(xlCellTypeBlanks)(1).Row
End If
Exit Function
NoInternalBlanks:
FirstEmptyRow = Cells(Rows.Count, Col).End(xlUp).Row + 1
End Function

Just call it from your own code and pass it the column number or letter (in
quotes). For example...

Sub YourMacro()
MsgBox "First empty row in Column "G" = " & FirstEmptyRow("G")
End Sub

NOTE: This function does NOT consider a cell with a formula that is
displaying nothing ("") to be blank.

Rick


"kleysonr" wrote in message
...
How can i get the number of the first empty row in a column ?