Looping through cells in Excel in two dimensions VBA

Question

I'm trying to loop through a set of cells using VBA in a worksheet and check if they contain any data. My spreadsheet is like:

   __A_____B_____C_____D____E_____F____G
 1| 3122  -1    -1   3243   
 2| -1    -1    -1   3243   1     1    1
 3| -1    -1    -1   3255         1
 5| 4232 2132   -1   3259
 6| 7544 1333   324  3259
 7| -1    -1    -1   3259   1     2    1
 8| -1    -1    -1   3259   1     1    1
 9| -1    -1    -1   3267
10| 2121  222   -1   3267

I want to get rid of the rows that don't have any data in columns E F and G but I'm not sure how to loop through the rows and columns. I have seen many instructions for looping down a column but I can't find anything on how to loop in two dimensions checking the cells for data.

Thanks

Answer 1

The basic idea of looping over rows and columns is that you need two for loops.

The first loops over rows, the second over columns.

I don't use VBA enough to remember how rows get deleted, but if you loop backwards (as in the code below) you should never lose track of which row you're deleting.

The following code should work for your purposes (although it begs for refactoring!):
Edit: thanks to barrowc for the correction.

Sub remove_some_rows()
    Dim i As Long
    Dim j As Long

    Dim current_cell As Object

    Dim beg_row As Long
    Dim end_row As Long
    Dim beg_col As Long
    Dim end_col As Long

    beg_row = 1
    end_row = 10
    beg_col = 1
    end_col = 7

    Dim empty_col_counter As Integer

    For i = end_row To beg_row Step -1

        empty_col_counter = 0

        For j = end_col To beg_col Step -1
            Set current_cell = ThisWorkbook.ActiveSheet.Cells(i, j)

            If j > 4 And current_cell.Value = "" Then
                empty_col_counter = empty_col_counter + 1
            End If
        Next j

        If empty_col_counter = 3 Then
            current_cell.EntireRow.Select
            Selection.Delete
        End If

    Next i
End Sub

Answer 2

This should work:

Sub main()

Dim maxRow As Integer
Dim currentRow As Integer

With Worksheets("Sheet1")
    maxRow = .Range("A1").CurrentRegion.Rows.Count

    Dim i As Integer
    ' Start at the bottom and work upwards
    For i = maxRow To 1 Step -1
        ' 5 represents column E, 6 is column F and 7 is column G
        If (.Cells(i, 5).Value = "" And .Cells(i, 6).Value = "" And _
            .Cells(i, 7).Value = "") Then
            .Rows(i).Delete
        End If
    Next i
End With

End Sub

Because there are only three columns to check, it's easy to just use And to join the three checks together. In a more complex situation, a nested For loop as in Adam Bernier's answer would be better