Insert 2 blank rows after every currentregion

Question

I need to insert 2 blank rows after every current region of data in Excel.

Theoretically my code should work and insert it AFTER the data BUT after trying it out so many times, It inserts it BEFORE the data not after.

Where did I go wrong? Can anybody kindly guide me? Thanks!

Sub AutoInsert2BlankRows()

Selection.CurrentRegion.Select
SendKeys "^{.}"
SendKeys "^{.}"
SendKeys "~"

ActiveCell.EntireRow.Select
'this chooses the whole row

Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
End Sub

Here is my picture for further clarification. As you can see there are 3 distinct currentregions separated by a blank row. What i need is to insert 2 additional blank rows in addition to the already present blank row so as to make 3 blank rows between each currentregion. (Apologies if i wasnt clear enough earlier.)

Here is the link to the image!

What is `SendKeys "^{.}"` intended to do? Shouldn't it be `SendKeys "^{DOWN}"` ? See [Contextures SendKeys](http://www.contextures.com/excelvbasendkeys.html). — , Jul 09 '16 at 07:53
@Jeeped Actually, Sendkeys "^{DOWN}" doesnt work. Rather it scrolls all the way down to A1048576 which is definitely too far down away — NewLearner, Jul 09 '16 at 08:00
Check the URL on the image you are trying to post; it seems like whitespace. — , Jul 09 '16 at 08:10
@Jeeped `SendKeys "^{.}"` moves the cursor to the corner of a selected region, and onwards to other corners. — Andy G, Jul 09 '16 at 08:11
@AndyG - I honestly don't know if I should regret not knowing that or be proud of the fact. — , Jul 09 '16 at 08:14
I use it lots ;), it is particularly useful to move the selection across one column. — Andy G, Jul 09 '16 at 08:15
As a novice coder, what reason would you use `SendKeys` rather than the more obvious `.End(xlDown)` etc? — Chris Slade, Jul 09 '16 at 08:19
`Ctrl-.` moves to the next corner, not down to the end. Try it, select a rectangular region, then press `Ctrl-.` several times. — Andy G, Jul 09 '16 at 08:21
@ChrisSlade Seeing the posted image now, I see what you mean about `End(xlDown)` being more obvious, particularly if all the areas occupy the same span of columns. — Andy G, Jul 09 '16 at 08:26
I don't believe so. It would be nice if there were a `Corners` collection ;) — Andy G, Jul 09 '16 at 08:28
You may wish to note that your image of sample data contains both formulas and typed constants. — , Jul 09 '16 at 10:01

0m3r · Accepted Answer · 2016-07-10T07:09:48.417

Is this what your trying to do?

1st Example

Sub AutoInsert2BlankRows()

'   // Set Variables.
    Dim Rng As Range
    Dim i As Long

'   // Target Range.
    Set Rng = Range("A2:A10")

'   // Reverse looping
    For i = Rng.Rows.Count To 2 Step -1

'       // Insert two blank rows.
        Rng.Rows(i).EntireRow.Insert
        Rng.Rows(i).EntireRow.Insert

'   // Increment loop
    Next i


End Sub

Edit

To add two more blank rows after each blank row, try the following.

2nd Example

Sub AutoInsert2BlankRows()

'   // Set Variables.
    Dim Rng As Range
    Dim i As Long

'   // Target Range.
    Set Rng = Range("A2:A10")

'   // Reverse looping
    For i = Rng.Rows.Count To 2 Step -1

        If Cells(i, 1).Value = 0 Then

'          // Insert two blank rows.
            Rng.Rows(i).EntireRow.Insert
            Rng.Rows(i).EntireRow.Insert

        End If

'   // Increment loop
    Next i


End Sub

3rd Example

Option Explicit
Sub AutoInsert2BlankRows()
'   // Set Variables.
    Dim Rng As Range
    Dim i As Long

'   // Target Range.
    Set Rng = ActiveSheet.UsedRange

'   // Reverse looping
    For i = Rng.Rows.Count To 1 Step -1

'       // If entire row is empty then
        If Application.CountA(Rows(i).EntireRow) = 0 Then

'           // Insert blank row
            Rows(i).Insert
            Rows(i).Insert

        End If

    Next i

End Sub

Thank you @Om3r for your answer. but however i dont need to add 2 lines after every line of data. rather they are variable number of lines before it is required to add 2 blank lines. is there a way to solve that problem? — NewLearner, Jul 09 '16 at 08:02
Hi @Om3r your answer would work after a slight tweak of your code... Just need to change the if Cells (i,1) to (i,2) since the first column is blank. However, in my other works, the number of rows is not fixed but is variable, how would i go about setting the range? Since some sheets have only 20 rows but some go up to 500+ rows. Could you teach me how to go solve the issue of variance? Thank you! :) — NewLearner, Jul 09 '16 at 09:32
Thank you for your help @Om3r! It was very much well appreciated! — NewLearner, Jul 29 '16 at 14:43

score 2 · Answer 2 · 2016-07-09T09:59:35.937

2

If you grab all of the xlCellTypeConstants with Range.SpecialCells method within the Worksheet.UsedRange property, you will have a number of non-contiguous Areas. These equate to the Range.CurrentRegion property. Cycle through them and insert rows as you please.

Sub autoInsertTwoBlankRows()
    Dim a As Long
    With Worksheets("Sheet1")
        With .UsedRange.SpecialCells(xlCellTypeConstants)
            For a = .Areas.Count To 1 Step -1
                With .Areas(a).Cells(1, 1).CurrentRegion
                    .Cells(.Rows.Count, 1).Offset(1, 0).Resize(2, .Columns.Count).Insert _
                      Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                End With
            Next a
        End With
    End With
End Sub

If your data contains both formulas and typed constants then this is more appropriate.

Sub autoInsertTwoBlankRows()
    Dim a As Long, ur As Range

    With Worksheets("Sheet1").Cells
        With Union(.SpecialCells(xlCellTypeConstants), _
                   .SpecialCells(xlCellTypeFormulas))
            For a = .Areas.Count To 1 Step -1
                With .Areas(a).Cells(1, 1).CurrentRegion
                    .Cells(.Rows.Count, 1).Offset(1, 0).Resize(2, .Columns.Count).Insert _
                      Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                End With
            Next a
        End With
    End With
End Sub

When inserting rows try to work from the bottom to the top so that displacing the rows does not affect further operations. This is the reason I started with the last Area and worked towards the first.

^{data islands before autoInsertTwoBlankRows} ^{data islands after autoInsertTwoBlankRows}

edited Jul 09 '16 at 09:59

answered Jul 09 '16 at 08:07

Your code half worked! :) like it added 2 lines to row 11 but unfortunately it added it a 2nd time in between rows 2 and 3 not row 7. could you also explain more about the code? Like why did you use Resize and UsedRange properties? Could you enlighten me more about that? Thank you – NewLearner Jul 09 '16 at 08:25
Sorry - here is the link to the MSDN [Range.Resize property](https://msdn.microsoft.com/en-us/library/office/aa176182%28v=office.11%29.aspx). After reading the UsedRange link I provided, what part are you having trouble with? I cannot see how that possibly missed a blank row and inserted rows in the wrong place twice. – Jul 09 '16 at 08:29
Dear Jeeped, I've tried hard testing it but it really does not work on my computer and Excel. maybe i could send you my file which contains the code you have? It still shows the same errors as before... also sorry for the disturbance, but why is your resize (2,1). If i understand what i read, would the (,1) result in an additional column every row? Also the .entirerow.insert what is its purpose given that the resize has already added the 2 blank rows? Sorry for the inconvenience and thank you for your help. I really appreciate it! :) – NewLearner Jul 09 '16 at 09:05
If you have two different areas that share a common row, rows will be inserted at the end of each area. This splits the areas up. – Jul 09 '16 at 09:15
@ThomasInzina - I'm going by what the OP has supplied in the image. But you are correct, it would probably be more universal if the new rows were inserted only as wide as the currentregion being worked on. – Jul 09 '16 at 09:16
Hmm...I think yours would be the best if you were to do this:`.Areas(a).Cells(.Areas(a).Cells.Count).Offset(1, 0).Resize(2, .Areas(a).Cells.Columns.Count).Insert` – Jul 09 '16 at 09:23
No, that would still break up a wider area below it. – Jul 09 '16 at 09:26

score 1 · Answer 3 · 2016-07-09T09:07:48.490

Updated: Thanks for the catch.

   Sub AutoInsert2BlankRows()
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With

    Dim lastRow As Long, x As Long

    lastRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

    For x = lastRow To 2 Step -1

        If WorksheetFunction.CountA(Rows(x)) > 0 And WorksheetFunction.CountA(Rows(x + 1)) = 0 Then
            Rows(x + 1 & ":" & x + 2).Insert Shift:=xlDown
        End If

    Next

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With

End Sub

/pre>

Two rows were inserted after A, B, C and E but not between D and E because they overlap.

Hi Thomas, your code only works on the assumption that the first column contains data. However, in my example there is a blank column. i.e. A is empty. How do we edit it so that it can work despite containing an empty column A before the data starts on column B and onwards? Thank you. (why did you use CountA not just Count? Can you enlighten me on this?) Thank you! — NewLearner, Jul 09 '16 at 08:44
Thank you very much for your help @Thomas! It was very much well appreciated! :) — NewLearner, Jul 29 '16 at 14:44

Andy G · Answer 4 · 2016-07-09T08:35:43.903

0

(What is "~" doing?)

Make sure that the Selection is in the region somewhere. With your code Ctrl-. is probably not navigating to the last cell, depending on where the activecell is when you run it. I would use:

Dim rng As Range
Application.ScreenUpdating = False
Set rng = Selection.CurrentRegion
Set rng = rng(rng.Count + 1)    'the last cell + 1 row
rng.EntireRow.Rows("1:2").Insert shift:=xlDown

edited Jul 09 '16 at 08:35

answered Jul 09 '16 at 08:10

Andy G

19,232
5
47
69

Hi @AndyG ~ is the command for Enter in Sendkeys. Like when you press the enter button to move on to the next row? Thank you for your help – NewLearner Jul 09 '16 at 08:14
I think it is `"{Enter}"`, a brief search will confirm this. I'm not sure why you need it though. Using `rng.Count` as in my code locates the last cell of a region, `+ 1` locates the following row. – Andy G Jul 09 '16 at 08:17
Your code works in theory but idk why it doesnt work in real life... and also could it be set rng = rng(rng.Count + 2) because the start of the next region of data is 2 lines away not 1? Im not very sure about this could you enlighten me about this? Thank you – NewLearner Jul 09 '16 at 08:33
Oops, I've edited it to use `EntireRow`, it was only pushing down in a single column. I don't need to use `+2` because I am inserting two rows "1:2". (Of course, we are relying on Selection being in the correct region.) – Andy G Jul 09 '16 at 08:35
Hi Andy G ive tried your code but i am still unable to get the desired results. Could this be due to the fact that you set rng to be selection.currentregion that means that it would continuously only add 2 rows after the 1st region, therefore being unable to add 2 rows in between other regions? How would we go about tackling this problem of currentregion and being able to bridge to other regions too? Thanks for sharing your knowledge! – NewLearner Jul 09 '16 at 08:48
My code is an alternative to your original code, and answers the related question as posted. Others have attempted to provide an entire coded solution for all regions. – Andy G Jul 09 '16 at 10:52

Chris Slade · Answer 5 · 2016-07-09T08:54:20.490

0

This worked for me, using Excel 2007.

Sub AutoInsert2BlankRows()
Dim rng As Range

Set rng = Selection.End(xlDown).EntireRow
rng.Offset(1).Insert Shift:=xlDown
rng.Offset(1).Insert Shift:=xlDown

End Sub

I've adapted and simplified the code in the question, mainly to avoid selecting cells. The user has selected a cell in the region that they want to insert two rows after. The variable rng is first moved to the bottom of the region, then the entire row is selected. The two lines are inserted before rng where rng has been offset by one row to ensure that they are after the region of interest. I'm sure the two lines could be inserted as a single command, but I don't know how, yet.

edited Jul 09 '16 at 08:54

answered Jul 09 '16 at 08:13

Chris Slade

309
3
9

hi Chris, your code unfortunately gives me errors... in the 3rd and 2nd last rows, the error "1004" application defined or object defined error shows.... – NewLearner Jul 09 '16 at 08:18
Odd, it works fine for me. I'm using Excel 2007, it might be different to your version. – Chris Slade Jul 09 '16 at 08:23
Im using Excel 2013 and yeah that maybe a reason why it causes so much confusion. Anyways thank you for your help and I appreciate it! Have a nice day! – NewLearner Jul 09 '16 at 08:27
"While this code block may answer the question, it would be best if you could provide a little explanation for why it does so." – RHA Jul 09 '16 at 08:40

score 0 · Answer 6 · answered Jul 09 '16 at 10:41

this will not add extra lines after the last "current region"

Sub AutoInsert2BlankRows()
    With Worksheets("mySheet").UsedRange '<-- change "mySheet" as per your actual sheet name
        With .Offset(, .Columns.Count).Resize(, 1)
            .FormulaR1C1 = "=IF(counta(RC1:RC[-1])>0,1,"""")"
            .Value = .Value
            With .SpecialCells(xlCellTypeBlanks).EntireRow
                .Insert Shift:=xlDown
                .Insert Shift:=xlDown
            End With
            .Clear
        End With
    End With
End Sub

@NewLearner, did you try this? – user3598756 Jul 15 '16 at 17:35 — user3598756, Jul 15 '16 at 17:35

Insert 2 blank rows after every currentregion

6 Answers6

Linked