Excel VBA循环范围和跳过空白

Question

我有低于VBA代码循环通过范围在单元格L26及以下，它工作正常，但我想跳过，如果单元格是空白，我已经谷歌搜索，并发现此代码：

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

如何将上面的代码修改成我的vba代码如下，感谢您的建议和帮助，谢谢。

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 

Answer 1

看到代码的评论我下面的代码，解释（有些问题）：

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 

Answer 2

我认为这应该做到这一点。

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