How can I repeat code through entire data?

Question

I have written a few lines of code that work like I want them too but I don't know how to repeat it through all rows of my data. This probably seems like a rather simple thing but since I started VBA just a few days ago I struggle with this line of code

If I continue with ActiveCell.Offset(-1,-4) after my code it's a bug and I don't know how to repeat the code through all rows.

Sub SelectRowsWithNoBlanks()
    Range("A2").Select
    
    If ActiveCell.Offset(0, 0).Value <> "" And ActiveCell.Offset(0, 1) <> "" And ActiveCell(0, 1) <> "" And ActiveCell(0, 1) <> "" Then
        Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column + 4)).Select
    End If
End Sub

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) • Completely avoid `Select` and `ActiveCell` and your live will be **a lot** easier. — Pᴇʜ, Jan 21 '19 at 13:37
Look into a `For` loop to go through a `Range` of cells. Good starting point. — JvdV, Jan 21 '19 at 13:43
@AnnavonBlohn If I right understand, you want to add to selection the cells from A to D columns in the rows where cells in A and B columns are empty? — omegastripes, Jan 21 '19 at 13:45
@omegastripes I am trying to select all rows that have no blank cells, so all data sets that are complete — Roxana, Jan 21 '19 at 13:54
@AnnavonBlohn Please show a screenshot of your data and tell which data range you want to check for blank cells. Otherwise it your information too unspecific to give a definite answer. — Pᴇʜ, Jan 21 '19 at 14:16
@SiddharthRout no of course not. I don't have the data yet but am supposed to "prepare" the code for another person. So the instructions I have are: Program a macro that selects all complete data sets (rows without blanks) and exports those to an existing Sharepoint List. After that the exported row should be deleted in Excel — Roxana, Jan 21 '19 at 14:37
when you say rows without blank, which columns are we looking at? A-D? A to Z? — Siddharth Rout, Jan 21 '19 at 14:40
@SiddharthRout As I don't have Access to the data yet I can't tell. But I thought extending the code for more columns later on wouldn't be a problem. So in the code I have written now I was checking for the columns A-D but I thought I could easily add the "checking" for more columns if needed — Roxana, Jan 21 '19 at 14:44

Siddharth Rout · Answer 1 · 2021-11-12T11:10:06.370

@SiddharthRout As I don't have Access to the data yet I can't tell. But I thought extending the code for more columns later on wouldn't be a problem. So in the code I have written now I was checking for the columns A-D but I thought I could easily add the "checking" for more columns if needed – Anna von Blohn 43 secs ago

In that case, here is a sample code.

Logic

As @Pᴇʜ mentioned avoid the use of .Select. Work with the objects.
Find the last row and loop through the rows. To find the last you you may want to see This
One way (which I am using) is to count the number of cells which are filled using Application.WorksheetFunction.CountA. So if it is columns A to D then there should be 4 cells to be filled to consider the "row" as filled. Similarly for Cols A to E, there should be 5 cells to be filled to consider the "row" as filled as so on.

Code

I have commented the code. So if you have a problem understanding it, let me know.

Option Explicit

Sub SelectRowsWithNoBlanks()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Dim myRange As Range, rng As Range
    
    '~~> Change this to the relevant sheet
    Set ws = Sheet1
    
    With ws
        '~~> Find the last row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Loop through the rows
        For i = 2 To lRow
            '~~> Change this as applicable
            Set rng = .Range("A" & i & ":D" & i)
            
            '~~> Check if the range is completely filled
            If Application.WorksheetFunction.CountA(rng) = rng.Columns.Count Then
                '~~> Store the range in a range object
                If myRange Is Nothing Then
                    Set myRange = rng
                Else
                    Set myRange = Union(myRange, rng)
                End If
            End If
        Next i
    End With
    
    'If Not myRange Is Nothing Then Debug.Print myRange.Address
    
    '~~> Check if any filled rows were found
    If Not myRange Is Nothing Then
        With myRange
            '
            '~~> Do what you want with the range
            '
        End With
    Else
        MsgBox "No filled rows were found"
    End If
End Sub

How can I repeat code through entire data?

1 Answers1