CODEWITHSUNDEEP
vbaexcel
robin
robin

Reputation: 181

Excel VBA Loop Range and skip blank

I have below vba code to loop thru range in cell L26 and below, is it working fine but i would like to skip if the cell is blank, i have googled and found this code:

For i = 10 To 49 
    Cells(i, 4).Select 
    If Cells(i, 4).Value <> "" Then 
        code.... 
    End If

How to modify above code into my vba code below, would appreciate your advise and help, thank you.

Sub PLANNER()

Dim loopRng As Range
Dim r As Range

Application.ScreenUpdating = False
For Each r In Sheet7.Range("L26", Range("L" & Rows.Count).End(xlUp))
    Sheet7.Range("L19").Value = r.Value
    Set loopRng = Worksheets("setting").Range("L21")
    ActiveWindow.ScrollRow = loopRng
    Application.CutCopyMode = False
    Call planner_Mail
Next r
Sheet2.Range("D2").Select

End Sub

Upvotes: 1

Views: 6948

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

See my code below, explanation (and some questions) in the code's comments:

Option Explicit

Sub PLANNER()

Dim loopRng As Range
Dim R As Range
Dim Sht As Worksheet

Application.ScreenUpdating = False

Set Sht = Sheet7 ' <-- make sure this is the worksheet you have your data
With Sht
    For Each R In .Range("L26", .Range("L" & .Rows.Count).End(xlUp))
        If Trim(R.Value) <> "" Then ' check that cell is not blank
            .Range("L19").Value = R.Value
            Set loopRng = Worksheets("setting").Range("L21") ' Not sure if Sheet7 = Worksheets("setting")
            ActiveWindow.ScrollRow = loopRng
            Application.CutCopyMode = False
            planner_Mail ' you can "drop" the "Call"
        End If
    Next R
End With
Application.ScreenUpdating = True

End Sub

Upvotes: 1

dwirony
dwirony

Reputation: 5450

I think this should do it.

Sub PLANNER()
Dim loopRng As Range
Dim r As Range

Application.ScreenUpdating = False
  For Each r In Sheet7.Range("L26", Range("L" & Rows.Count).End(xlUp))
      If r.Value <> "" Then
          Sheet7.Range("L19").Value = r.Value
      End If
  Set loopRng = Worksheets("setting").Range("L21")
 ActiveWindow.ScrollRow = loopRng
 Application.CutCopyMode = False
  Call planner_Mail
Next r

Sheet2.Range("D2").Select
End Sub

Upvotes: 1

Related Questions