VBA Nested Do Until Loop With For Each Loop

Question

The Do Until statement works perfectly; however, I cannot continue past the first worksheet. Thank you in advance for any help.

Option Explicit
  Sub InsertBlankRow()
    Dim rowCount As Long
    Dim ws As Worksheet

   For Each ws In ThisWorkbook.Worksheets
     rowCount = 2

        With ws

      Do Until IsEmpty(Cells(rowCount + 1, "D"))
         If (Cells(rowCount, "D") <> Cells(rowCount + 1, "D")) Then
            Range(Cells(rowCount + 1, "D"), Cells(rowCount + 2, "D")).EntireRow.Insert Shift:=xlDown
            rowCount = rowCount + 3
        Else
            rowCount = rowCount + 1
        End If
    Loop
    End With
Next ws
End Sub

Thank you all for the responses. Using the ws.Cells and ws.Rows caused the Do Until statement to not work. Once I removed ws. the rows were able to be added. It still will not loop through all the worksheets though.

Edited the code to provide what I am currently working with.

qualify each `Cells` reference with `ws`. So `IsEmpty(ws.Cells(rowcount + 1, "D"))` and so on. Otherwise the code only looks at the ActiveSheet when the macro is run. Also read up on [avoiding Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Scott Holtzman, Dec 22 '16 at 17:09

score 0 · Answer 1 · answered Dec 22 '16 at 17:16

You are increasing row count in first sheet and I am guessing your next sheet/s do not have more rows compare to first sheet. So move your rowCount inside For Each Loop, like this:

'rowCount = 2  'set to start row  'REMOVE FROM HERE

For Each ws In ThisWorkbook.Worksheets

    rowCount = 2  'set to start row
    Do Until IsEmpty(Cells(rowCount + 1, "D"))

So on every sheet row will start from 2. Also incorporate all the changes suggested in comments.

Shai Rado · Answer 2 · 2016-12-22T18:28:55.143

Following-up on Scott^2 (Holtzman and Craner), you need to reference all objects inside your For Each loop. I prefer using the With ws , it cleans and simplifies your code, all objects nested inside the With statement have . as a prefix (.Cells(rowCount, "D") and .Rows(rowCount + 1).Insert Shift:=xlDown).

Also, there is no need to select the row before inserting 2 rows in between (always better to avoid using Select and Selection), you can directly use .Rows(rowCount + 1).Insert Shift:=xlDown.

Full "Clean" code

Option Explicit

Sub InsertBlankRow()

    Dim rowCount As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        rowCount = 2  'set to start row

        With ws
            Do Until IsEmpty(.Cells(rowCount + 1, "D"))

                If .Cells(rowCount, "D") <> .Cells(rowCount + 1, "D") Then
                    .Rows(rowCount + 1).Insert Shift:=xlDown
                    .Rows(rowCount + 1).Insert Shift:=xlDown
                    rowCount = rowCount + 3
                Else
                    rowCount = rowCount + 1
                End If
            Loop
        End With
    Next ws

End Sub

Extra: if you want to insert 2 lines in between different values in column D with 1 code line, use the code line below :

.Range(.Cells(rowCount + 1, "D"), .Cells(rowCount + 2, "D")).EntireRow.Insert Shift:=xlDown

Thank you all for the responses. – Elisabeth Curtis Dec 22 '16 at 20:54 — Elisabeth Curtis, Dec 22 '16 at 20:54
@ElisabethCurtis you're welcome, please mark as answer – Shai Rado Dec 25 '16 at 04:08 — Shai Rado, Dec 25 '16 at 04:08

VBA Nested Do Until Loop With For Each Loop

2 Answers2