CODEWITHSUNDEEP
excelvbaloops
Linify
Linify

Reputation: 237

How to end a do while loop in VBA

I would like to know what is wrong with my coding as I am unable to end my do while loop in Microsoft Excel VBA. I wish to end this do while loop if the next line is blank.

Do While Cells(RowName, 1) <> ""
    Name = Cells(RowName, ColumnName)
    MsgBox Name
    RowName = RowName + 1
Loop

Please enlighten me as I am still a beginner. The MsgBox kept popping out and does not end even if it is blank.

Upvotes: 12

Views: 13816

Answers (3)

Sankar Balasubramanian
Sankar Balasubramanian

Reputation: 103

"Exit Do" - You can use this command wherever you want your loop to be stopped.

Sub ExitDoSample
    Dim rowname As Integer
    Dim ColumnName  As Integer
    rowname = 1
    ColumnName = 1


    Do While Cells(RowName, 1) <> ""

        Name = Cells(rowname, ColumnName).Value
        If Name = "" Or IsEmpty(Name) Then
            Exit Do
        Else
            MsgBox Name
        End If
        rowname = rowname + 1
    Loop
End Sub

Upvotes: 6

D_Bester
D_Bester

Reputation: 5891

Sankar Balasubramanian's answer is very close but has a few problems. Here's how I would do it. Do while not empty and Exit Do if trimmed value is blank string. 

Sub SampleEnding()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim RowNum As Long: RowNum = 1
    Dim ColNum As Long: ColNum = 3
    Dim Name As String

    Do While Not IsEmpty(ws.Cells(RowNum, 1))
        If Trim(ws.Cells(RowNum, 1).Value) <> "" Then
            Name = ws.Cells(RowNum, ColNum)
            MsgBox Name
        Else
            Exit Do
        End If
        RowNum = RowNum + 1
    Loop

End Sub

RowNum should always be declared as Long to avoid overflow error as Excel has more than a million rows.

Also it's better and clearer if you declare and set a worksheet variable. You should always avoid unqualified range/cells references.

Upvotes: 4

HedgePig
HedgePig

Reputation: 468

You are checking whether or not that the row RowName in column 1 is blank but your are picking up the name from column ColumnName, which may not be column 1. So column 1 may have data in it (so the check passes and the loop continues) but column no. ColumnName may be blank so your message box shows something blank. Could this be the problem?

Upvotes: 0

Related Questions