How can i count an active cell row?

[Geokakis]

Hi guys,

What code should i use to count the excel row and to make the cursor jump automatically to the next empty row?

can you help? examples with detail code will be very helpfull.

Geo

 

[Nikita6003]

Hiya,

I've got 2 examples: one uses the UsedRange of the worksheet (problem with Usedrange can be that if your sheet starts with one or more empty rows/columns these are not taken into account, giving you the wrong results). The other uses the GotoSpecialCells option (press F5 and click on Special .., then choose last Call in workbook). That one will ALWAYS give you the correct address for the last used cell in your sheet

Here goes:

Sub UseSpecialCells()
    Dim l_sLastCellAddress As String
    Dim l_lNextEmptyRow As Long
    
    l_sLastCellAddress = Selection.SpecialCells(xlCellTypeLastCell).Address
    l_lNextEmptyRow = Selection.SpecialCells(xlCellTypeLastCell).Row + 1
    MsgBox "Last cell in sheet: " & l_sLastCellAddress & ". First empty row: " & l_lNextEmptyRow
    
End Sub

Sub UseUsedRange()
    Dim l_wksTest As Worksheet
    Dim l_sLastCellAddress As String
    Dim l_lNextEmptyRow As Long
    
    Set l_wksTest = ThisWorkbook.Sheets(1)
    l_sLastCellAddress = l_wksTest.Cells(l_wksTest.UsedRange.Rows.Count, l_wksTest.UsedRange.Columns.Count).Address
    l_lNextEmptyRow = l_wksTest.UsedRange.Rows.Count + 1
    MsgBox "Last cell in sheet: " & l_sLastCellAddress & ". First empty row: " & l_lNextEmptyRow
    Set l_wksTest = Nothing
    
End Sub

HTH

Cheers
Nikki

 

[Geokakis]

Lots,..........., and lots of thanks Nikki !!!

Geo