help with Excel macro counter 1

[PJFry]

In a training worksheet, data is on lines 14-24
Below macro returns a count of 4 lines (but there are actually 11 lines of data.

Sub LineCnt()

    Cells(14, 1).Select
    
    Dim r As Integer
    r = 1
               
    Do While Cells(r, 1).Value <> ""
        r = r + 1
    Loop
      
    MsgBox ("Lines found: " & r)
    
End Sub

 

[SkipVought]

Hi,

First, [tt]Cells(14, 1).Select[/tt] does nothing for the count.

The cells that are counted are from A1 downward until an empty cell is encountered.

Skip,

_[red][/red]

 

[macropod]

Hi PJFry,

A couple of points:
. there is no need to select any cells; and
. your counter is initializing the count at row 1 (r = 1) and counting from there until the first empty cell is found.

Try something along the lines of:

Sub LineCnt()
    Dim StartRow As Integer
    Dim EndRow As Integer
    StartRow = 14
    EndRow = StartRow
    Do While Cells(EndRow, 1).Value <> ""
        EndRow = EndRow + 1
    Loop
    MsgBox ("Lines found: " & EndRow - StartRow)
End Sub

Cheers

[MS MVP - Word]