looping through an unknown range of rows in excel

Question

say i have 5 columns and god knows how many row. All the cells in this range are populated except for a few cells in the last column. i need to loop through all the rows (starting in A1) until i get to the last row, and for each row that has a cell populated in the last column, display a message box saying "hello"

I'm just really unsure how to start the loop. I've tried googling, but I dont understand. I know how i'd check for empty cells, and how to diply the message box, but not how to find the end of the range.

http://stackoverflow.com/questions/6301665/how-to-count-the-number-of-rows-in-excel-with-data — Dane I, Apr 23 '15 at 03:41

score 0 · Answer 1 · answered Apr 23 '15 at 03:43

As long as there are no completely blank columns or rows the .currentregion property should give you a reference to the complete rectangular block of cells.

dim rw as long
with activesheet.cells(1, 1).currentregion
    for rw = 1 to .rows.count
        if not isempty(.cells(rw, 5)) then
            msgbox .cells(rw, 5).address(0, 0) & " has a value (" & .cells(rw, 5).value & ")"
        end if
    next rw
end with

score 0 · Accepted Answer · answered Apr 23 '15 at 04:58

additional variant from my side

Dim cl As Range
With ActiveSheet
    For Each cl In .Range(.[A1].CurrentRegion.Columns(5).Address)
        If cl.Value <> "" Then
            MsgBox cl.Address(0, 0) & " has a value (" & cl.Value & ")"
        End If
    Next cl
End With

score 0 · Answer 3 · answered Apr 23 '15 at 07:42

Lots of versions

Sub Button1_Click()
    Dim Rws As Long, Rng As Range, c As Range, x
    Rws = Cells(Rows.Count, "A").End(xlUp).Row
    Set Rng = Range(Cells(1, 1), Cells(Rws, 1))

    For Each c In Rng.Cells
        x = WorksheetFunction.CountA(Range(Cells(c.Row, 1), Cells(c.Row, 5)))
        If x = 5 Then MsgBox "Hello! Row #- " & c.Row & " has 5!"
    Next c

End Sub

looping through an unknown range of rows in excel

3 Answers3