11

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.

Luuklag
  • 3,897
  • 11
  • 38
  • 57
Linify
  • 227
  • 4
  • 13

3 Answers3

4

"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
Sankar Balasubramanian
  • 83
  • 8
  • Thank you. I understood better through coding. – Linify Apr 29 '16 at 03:37
  • Your entire sub needs to be indented at least once to trigger code block when posting an answer here. – D_Bester Apr 29 '16 at 03:38
  • 2
    `Do While Cells(1, 1) <> ""` should be changed to: `Do While Cells(RowName, 1) <> ""` – D_Bester Apr 29 '16 at 03:50
  • @D_Bester Thank you. However, does it matter if I do not dim RowName as Integer? – Linify Apr 29 '16 at 03:55
  • 1
    @Linify RowName should be renamed as RowNum and declared as long: `Dim RowNum as Long`. There are more rows then integer can handle. There's no reason to declare as string although it's possible. It's not efficient because the program has to keep converting between string and number. – D_Bester Apr 29 '16 at 04:02
  • @D_Bester okay understood. Thank you – Linify Apr 29 '16 at 04:08
  • @D_Bester . you are right if we change the Do While Cells(1, 1) <> "" to Do While Cells(RowName, ColumnName) <> "", then the Exit do is not at all required. – Sankar Balasubramanian Apr 29 '16 at 04:10
3

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.

D_Bester
  • 5,723
  • 5
  • 35
  • 77
  • Does it matter if I have more than one worksheet in an excel for the first sentence of coding? – Linify Apr 29 '16 at 04:27
  • You can have many worksheets; this code (same as yours) will operate on the active sheet. But this has the advantage that you could specify any sheet; it doesn't have to be active. – D_Bester Apr 29 '16 at 04:30
  • 1
    You can also declare column name as string and assign column letter. That works fine with Cells(). `Dim ColumnName As String: ColumnName = "C": Name = ws.Cells(RowNum, ColumnName)` Gets name from column C. – D_Bester Apr 29 '16 at 04:41
  • May I ask if adding the `.Value` necessary as I tried to remove it and it works too in the coding? – Linify May 03 '16 at 03:33
  • `.Value` is totally optional in VBA as it is the default. Just being explicit there. In VB.net it is not optional so I'm getting used to adding it. – D_Bester May 03 '16 at 09:32
0

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?

HedgePig
  • 469
  • 3
  • 10