Looping over every row until last line of data

Question

I have three macros that work for my first row. I want to create a loop to make that code run for all the rows until the last row of input.

It prints what is missing when an empty cell is found in columns B, D and E. (I will eventually put the three macros into one to make it more concise.)

This is the code for the first row:

'checking if PayorID is empty
Sub error_field()
    Sheets("1099-Misc_Form_Template").Select
    Range("A2").Select
    If Range("A2").Value = "" Then
    ActiveCell.Value = ActiveCell.Value & "PayorID"
    End If
End Sub

'checking if TIN is empty
Sub error_field2()
    Sheets("1099-Misc_Form_Template").Select
    Range("A2").Select
    If Range("E2").Value = "" Then
    ActiveCell.Value = ActiveCell.Value & ", TIN"
    End If
End Sub

'checking if AccountNo is empty
Sub error_field3()
    Sheets("1099-Misc_Form_Template").Select
    Range("A2").Select
    If Range("F2").Value = "" Then
    ActiveCell.Value = ActiveCell.Value & ", AccountNo"
    End If
End Sub

This is what I tried for a loop:

'repeating for all rows
Sub repeat_all_rows()
Dim sh As Worksheet
Dim rw As Range
Dim RowCount As Integer

RowCount = 0

Set sh = ActiveSheet
For Each rw In sh.Rows

    If Range("A2").Value = "" Then
        ActiveCell.Value = ActiveCell.Value & "PayorID"
    If Range("E2").Value = "" Then
        ActiveCell.Value = ActiveCell.Value & ", TIN"
    If Range("F2").Value = "" Then
        ActiveCell.Value = ActiveCell.Value & ", AccountNo"
        Exit For
    End If

    RowCount = RowCount + 1

Next rw

End Sub

I want code to be performed on all the rows until the last row of data is found.

First thing will be to [avoid using activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. There are several ways of [finding the last row](https://stackoverflow.com/questions/39470412/last-row-in-column-vba) and you can then loop from the first one to the last one — cybernetic.nomad, Jul 09 '19 at 18:43

score 1 · Accepted Answer · answered Jul 09 '19 at 18:47

Something like this should work:

'repeating for all rows
Sub repeat_all_rows()

    Dim sh As Worksheet
    Dim rw As Range
    Dim RowCount As Integer

    RowCount = 0

    Set sh = ActiveSheet
    For Each rw In sh.UsedRange.Rows

        FlagMissing rw, "A", "Payor ID"
        FlagMissing rw, "E", "TIN"
        FlagMissing rw, "F", "AccountNo"

    Next rw

End Sub

Sub FlagMissing(rw As Range, col as String, Flag As String)
    If Len(Trim(rw.cells(1, col).value)) = 0 Then
        With rw.Cells(1)
            'add the flag with a comma if there's already content there
            .Value = .Value & IIf(.Value="", "", ", ") & Flag
        End with
    End If
End sub

...though I've not accounted for that Exit For in your posted code.

Thanks, I got it from that :) – Starbucks Jul 10 '19 at 15:16 — Starbucks, Jul 10 '19 at 15:16

Looping over every row until last line of data

1 Answers1