Excel Macros: Loop through rows

Question

I'm working on another Excel project and, this time, I dipped my feet into VBA macros for the first time. I had very basic knowledge of VB but it's been more than a decade since I used the language so I'm rather lost.

Anyway, I have the following function that I use to append a row to another table. It works flawlessly but I'm not comfortable with how inelegant it looks:

Sub setRecord()
    If [K2].Value <> "" Then
        appendToRecord "RecordsTable", [E2:K2].Value
    End If
    If [K3].Value <> "" Then
        appendToRecord "RecordsTable", [E3:K3].Value
    End If
    If [K4].Value <> "" Then
        appendToRecord "RecordsTable", [E4:K4].Value
    End If
    If [K5].Value <> "" Then
        appendToRecord "RecordsTable", [E5:K5].Value
    End If
    If [K6].Value <> "" Then
        appendToRecord "RecordsTable", [E6:K6].Value
    End If
End Sub

I'm sure I'm not the only one who feels the same way...

I do have a fixed number of rows right now. However, this may change in the future and I'd like my code to be ready for that without the need to add or remove an IF statement.

Basically, I check a certain cell within a row if it is empty and, if it isn't, I run another function to add the values in that row to a new row in a table.

So, my question is, is there a way to loop through a range of rows like a For or ForEach loop while checking a value in a certain column along the way so that I can make this piece of code shorter/more efficient in VBA? Any pointers will be highly appreciated. I'm using Office 365, by the way.

Answer 1

VBA's FOR construct looks like this:

FOR counter = start TO end [Step increment]
 {...statements...}
NEXT [counter];

example usage:

Dim myCount As Integer
For myCount = 1 to 5
  MsgBox (myCount)
Next myCount

For your problem, you could start in a certain cell, then set up the loop to

a) check the Active Cell, then

b) use VBA's OFFSET function to move to the next cell in the range.

Answer 2

Here's the solution I came up with:

Dim RecordRange As ListObject
Set RecordRange = Worksheets("Set Record").ListObjects("NewRecord")

For i = 1 To RecordRange.DataBodyRange.Rows.Count
    If RecordRange.DataBodyRange.Cells(i, RecordRange.ListColumns("N").Index) <> "" Then
        appendToRecord "RecordsTable", RecordRange.DataBodyRange.Rows(i)
    End If
Next i

I tried to use the resources in the answer that @mcalex provided but I couldn't get it to work - that's mainly because of my lack of experience, though. Fortunately, I found a few articles about VBA and tables that helped me figure up an answer for myself.