Stop when No more data exists below

**Jim28** · 12-02-2010, 03:58 PM

Dear all

I want some code that exits a Do...loop when there is no more data below the activecell in column A? That is no data at all anywhere below the activecell in column A.

Is this possible?

Thanks

JAmes

**jaslake** · 12-02-2010, 04:16 PM

Hi Jim28

Like this?

Option Explicit
Public Sub test()
    Dim LR As Long
    Dim i As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    i = 1
    Do
        Cells(i, 1) = i
        i = i + 1
    Loop Until i = LR + 1
End Sub

The code finds the last row used in column A and performs the loop until it finds it.

John

**Jim28** · 12-02-2010, 04:42 PM

Originally Posted by jaslake

Hi Jim28

Like this?

Option Explicit
Public Sub test()
    Dim LR As Long
    Dim i As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    i = 1
    Do
        Cells(i, 1) = i
        i = i + 1
    Loop Until i = LR + 1
End Sub

The code finds the last row used in column A and performs the loop until it finds it.

John

Thanks John is it possible to do it using activecells?

**Richard Buttrey** · 12-02-2010, 04:34 PM

Hi
This assumes your code moves the active cell on each iteration of the loop

Sub ExitWhenNoMoreData()
    Dim lCount As Long
    lCount = 1
    Do Until lCount = 0
        lCount = Application.WorksheetFunction.CountA(Range(ActiveCell, Range("A" & Rows.Count)))
        'your code'
    Loop
End Sub

HTH

**Jim28** · 12-02-2010, 04:53 PM

Sub Macro1()

Dim lcount As Long

Range("A1").Select

Do

    If ActiveCell <> 1 Then
        ActiveCell.Rows("1:1").EntireRow.Select
        Selection.Delete Shift:=xlUp
        ActiveCell.Select
    Else
        ActiveCell.Offset(1, 0).Select
    End If
 
 lcount = Application.WorksheetFunction.CountA(Range(ActiveCell, Range("A" & Rows.Count)))

Loop Until lcount = 0


End Sub

Perfect! Thanks :_