Excel vba to Insert blank rows is taking too long

Question

I have a macro that is comparing 2 cells and inserting a blank row between them if they are different. It was taking about 12 minutes to complete this process with this code:

    Worksheets("Dollars").Activate
    Range("B10").Select

'    Do Until ActiveCell.Formula = ""
'        DoEvents
'        If ActiveCell <> ActiveCell.Offset(1, 0) Then
'            ActiveCell.Offset(1, 0).Activate
'            Selection.EntireRow.Insert
'        End If
'        ActiveCell.Offset(1, 0).Activate
'    Loop

I rewrote the code to this way to see if it was any better and it still took over 12 minutes to run.

    Dim r As Long
    Dim vStr1 As String
    Dim vStr2 As String
    r = 10
    vStr1 = ""
    vStr2 = ""

    Do Until Len(Trim(Cells(r, 2))) = 0
        DoEvents
        vStr1 = ""
        vStr2 = ""
        vStr1 = Trim(Cells(r, 2))
        vStr2 = Trim(Cells((r + 1), 2))

        If vStr1 = vStr2 Then
'           do nothing
        Else
            Cells((r + 1), 1).EntireRow.Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            r = r + 1
        End If
        r = r + 1
    Loop

is there a better way to do this so it doesn't take so long? We are using Windows 10 and Office 2016. Thanks for the help. I appreciate it....

You want to loop from the bottom up if you're inserting rows. Also, don't Select. — BigBen
– BigBen, Commented Mar 18, 2020 at 20:31
Can you show sample data? You can do this with a UNION I believe but don't want to write up a solution before seeing a before and after — urdearboy
– urdearboy, Commented Mar 18, 2020 at 20:44
@PatrickHonorez the screenupdating will only make a small increase, as is usually considered a sloppy fix. The idea should be to fix the code in a way that that the code references the sheet minimally. — Scott Craner
– Scott Craner, Commented Mar 18, 2020 at 20:59
I am not saying it is bad, but as a silver bullet to speed up code, it is little like, "putting lipstick on a pig". I use it when I need it, but it is not as a cure all. — Scott Craner
– Scott Craner, Commented Mar 18, 2020 at 21:09
@ScottCraner haha!! I will remember the 'lipstick on a pig'. Good one :-) — Patrick Honorez
– Patrick Honorez, Commented Mar 19, 2020 at 14:12

urdearboy · Accepted Answer · 2020-03-18 20:51:38Z

1

Assuming you only care if A1 <> A2 and so on until the end of your range.... you can use a Union to gather up target cells where you want your rows to be inserted. Then, insert the rows all at once at the end rather doing so line by line. Notice that nothing needs to be selected as stated by @BigBen

Sub Social_Distance()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet2")

Dim lr As Long, MyUnion As Range, xCell As Range
lr = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

For Each xCell In ws.Range("A2:A" & lr)
    If xCell.Value <> xCell.Offset(1).Value Then
        If Not MyUnion Is Nothing Then
            Set MyUnion = Union(MyUnion, xCell.Offset(1))
        Else
            Set MyUnion = xCell.Offset(1)
        End If
    End If
Next xCell

If Not MyUnion Is Nothing Then MyUnion.EntireRow.Insert Shift:=xlDown

End Sub

answered Mar 18, 2020 at 20:51

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Shaves Over a year ago

@urdearboy...............Thanks............It has to compare each cell to the one below it. A1<> A2, A2 <> A3, A3 <> A4, etec.....and then insert a blank between any 2 cells that dont match...

urdearboy Over a year ago

Correct - that’s what is happening here :)

Shaves Over a year ago

@urdearboy......I added the code as is and ran the macro., I got 1900 blank rows between the what was the first and second cells. No blank rows between the rest of the data

urdearboy Over a year ago

I tested this on Column A for me using data a, a, a, b, b, c, c, c, c, d, d, e, e, e, f and this created a space between each new letter effectively creating groupings of letters

SJR Over a year ago

@urdearboy - I'd have thought that works but try it with a,b,c,d,e,f,g.

|

SJR · Accepted Answer · 2020-03-19 15:36:48Z

1

This will not be tremendously quick, but should do the job.

Sub x()

Dim r As Long

Application.ScreenUpdating = False

With Worksheets("Dollars")
    For r = .Range("B" & Rows.Count).End(xlUp).Row To 10 Step -1
        If .Cells(r, 2).Value <> .Cells(r - 1, 2).Value Then
            .Cells(r, 2).EntireRow.Insert
        End If
    Next r
End With

Application.ScreenUpdating = True

End Sub

answered Mar 19, 2020 at 15:36

SJR

23.1k6 gold badges21 silver badges26 bronze badges

1 Comment

urdearboy Over a year ago

This is the correct way to do this (loop backwards) but I don’t believe this will address the speed issue. Will prob take just as long, although at least it will be correct :) lol

Patrick Honorez · Accepted Answer · 2020-03-19 14:17:09Z

0

Generally speaking inserting a lot of rows in Excel is a PITA performance wise.
You should consider adding rows at the end of your list and sorting the whole list at the end of the process.
I know it's a bit short answer but it's all I can do from my Chromebook now...

answered Mar 19, 2020 at 14:17

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

2 Comments

Shaves Over a year ago

@patrickhonorez....I agree.....I'll give that a try and see how it works for me.....Thanks

Shaves Over a year ago

@patrickhomorez.........I used your suggestion and it worked for me. The old process was taking over 12 minutes and now it takes 4 - 5 seconds. Thanks for the suggestion.....

Collectives™ on Stack Overflow

Excel vba to Insert blank rows is taking too long

3 Answers 3

10 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related