Quickly select the 10th non-empty cell in column "A"

[harzer]

Hello everyone,
In column "A", I have a very large number of empty and non-empty cells, how can we quickly select the 10 non-empty cells in this column "A".
I've put together a little macro that does the job, but it takes a long time to run.
How can we do this more efficiently.
I want a solution in VBA, no formula please.
Thanks in advance.

Here is the Macro.

Private Sub CommandButton2_Click

    Application.ScreenUpdating = False

    Dim lastRow As Long
    Dim Count As Integer
    Dim Row As Integer

    Count = 0
    Row = 1
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    Do While Row <= lastRow
        If Not (Cells(Row, 1) = "") Then
            Count = Count + 1
        End If
        Row = Row + 1
                If Count = 10 Then
                     Range("A" & Row).Select
                End If
    Loop

    Application.ScreenUpdating = True

End Sub

 

[Peter_SSs]

Give this a try

Private Sub CommandButton2_Click()
  Dim a As Variant
  Dim i As Long, myCount As Long
  
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    Do
      i = i + 1
      If Len(a(i, 1)) > 0 Then myCount = myCount + 1
    Loop Until myCount = 10 Or i = UBound(a)
    If myCount = 10 Then
      .Cells(i).Select
    Else
      MsgBox "Ten cells not found"
    End If
  End With
End Sub

 

[harzer]

Hello Peter_SSs,
Thank you for your efficiency with the proposed code, in fact, it works quickly and gives me the desired result.
Thanks again.
Greetings.

 

[Peter_SSs]

You're welcome Thanks for the follow-up.