CODEWITHSUNDEEP
excelvba
Carlos80
Carlos80

Reputation: 433

VBA Loop until cell not blank

I'm sure this is going to be fairly straight forward but I am struggling with this logic. I'm trying to create a row count based on populated data and then loop through all the empty cells in until it fields that the notes column in populated. The idea is that this value/note is then written to a SQL table.

The code I have so far is:

NoteCount = WorksheetFunction.CountA(Sheets("Missing").Range("B10:B7500"))

Sheets("Missing").Range("L10").Select

For i = 10 To NoteCount    
    If ActiveCell.Value = "" Then    
        Next i    
    Else:    
        'SQL Code entered here'    
    End If    
Next I

I know this code is not working as my For Loop is not aligned but I'm just trying to show what I am trying to achieve.

Upvotes: 0

Views: 8096

Answers (2)

CallumDA
CallumDA

Reputation: 12113

You might find some methods are quite slow if you have a lot of data. This will be quicker than looping through every cell in the column:

Sub test()
    Dim rng As Range, found As Range
    Dim firstAddress As String

    With ThisWorkbook.Worksheets("Missing")
        Set rng = .Range(.Range("B10"), .Cells(.Rows.Count, "B").End(xlUp))
        Set rng = rng.Offset(0, 10)
    End With

    Set found = rng.Find("", , , xlWhole)
    If Not found Is Nothing Then
        firstAddress = found.Address
        Do
            'SQL code entered here
            Set found = rng.FindNext(found)
        Loop While Not found.Address = firstAddress
    End If
End Sub

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

This can be achieved with a little shorter and simplier, using the code below:

Dim i As Long

With Sheets("Missing")
    For i = 10 To .Cells(.Rows.Count, "B").End(xlUp).Row ' loop until last row with data in column "B" (skip blank rows)
        If Trim(.Range("L" & i).Value) <> "" Then ' check if value in cell in column "L" in current row is not empty
            'SQL Code entered here'
        End If
    Next i
End With

Upvotes: 3

Related Questions