Insert two empty lines only in the "N:T" columns

H

harzer

Board Regular
Joined
Dec 15, 2021
Messages
122
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone,
I have a group of columns "N:T" which contains a variable number of rows, I want to add two empty lines after each row in this group of columns starting from cell "N2" and ending at the last row of the of column “T”.
Data outside of the "N:T" columns should not be modified.
I would like to ask you for a solution with arrays (if it is possible) because there are a large number of lines to process.

Sheet before inserting the two empty lines

Permuter2Ranges.xlsm
ABCDEFGHIJKLMNOPQRSTUVWX
1abcdefghijklmJeunePèreMèreJeunePèreMère
2abcdefghijklmNM96-010/2021 FNM96-002/2020 MNM96-020/2020 FNM96-008/2021 MNM96-002/2020 MNM96-020/2020 Fnopq
3abcdefghijklmAE27-026/2022 FNM96-046/2019 MNM96-036/2021 FAE27-018/2022 MNM96-005/2021 MAE27-068/2021 Fnopq
4abcdefghijklmAE27-037/2022 FNM96-005/2021 MAE27-068/2021 FAE27-019/2022 MNM96-005/2021 MAE27-068/2021 Fnopq
Feuil1


Result after inserting the two empty lines

Permuter2Ranges.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXY
1abcdefghijklmJeunePèreMèreJeunePèreMère
2abcdefghijklmNM96-010/2021 FNM96-002/2020 MNM96-020/2020 FNM96-008/2021 MNM96-002/2020 MNM96-020/2020 Fnopqrstwxyz
3abcdefghijklmnopqrstwxyz
4abcdefghijklmnopqrstwxyz
5AE27-026/2022 FNM96-046/2019 MNM96-036/2021 FAE27-018/2022 MNM96-005/2021 MAE27-068/2021 F
6
7
8AE27-037/2022 FNM96-005/2021 MAE27-068/2021 FAE27-019/2022 MNM96-005/2021 MAE27-068/2021 F
9
10
11
Feuil1


Thank you in advance for your contributions.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Try this with a copy of your workbook.

VBA Code: 
Sub Insert_Rows()
  Dim a As Variant
  Dim i As Long, j As Long, k As Long, rws As Long
  
  Application.ScreenUpdating = False
  Columns("U").Insert
  rws = Range("T" & Rows.Count).End(xlUp).Row - 1
  ReDim a(1 To 3 * rws, 1 To 1)
  For i = 1 To 3
    For j = 1 To rws
      k = k + 1: a(k, 1) = j
    Next j
  Next i
  Range("U2").Resize(UBound(a)).Value = a
  Range("N2:U2").Resize(UBound(a)).Sort Key1:=Columns("U"), Order1:=xlAscending, Header:=xlNo
  Columns("U").Delete
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this:
VBA Code: 
Sub InsertCells()

    Dim lr As Long
    Dim r As Long
    Dim rng As Range
    
    Application.ScreenUpdating = False

'   Find last row in column N with data
    lr = Cells(Rows.Count, "N").End(xlUp).Row
    
'   Loop through all rows backwards, up to row 3
    For r = lr To 3 Step -1
'       Build range
        Set rng = Range("N" & r & ":T" & r + 1)
'       Insert cells
        rng.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Hello Peter_SSs,
Thank you for your reply. The code almost suits me because I forgot a small detail (sorry), namely:
In my example, it is true that the number of rows in columns "N:T" is identical, but it sometimes happens that it is greater in column "N", how can you get the largest number of rows in the set of all columns "N:T"

See this example:

Permuter2Ranges.xlsm
ABCDEFGHIJKLMNOPQRSTUVWX
1abcdefghijklmJeunePèreMèreJeunePèreMère
2abcdefghijklmNM96-010/2021 FNM96-002/2020 MNM96-020/2020 FNM96-008/2021 MNM96-002/2020 MNM96-020/2020 Fnopq
3abcdefghijklmAE27-026/2022 FNM96-046/2019 MNM96-036/2021 FAE27-018/2022 MNM96-005/2021 MAE27-068/2021 Fnopq
4abcdefghijklmAE27-037/2022 FNM96-005/2021 MAE27-068/2021 FAE27-019/2022 MNM96-005/2021 MAE27-068/2021 Fnopq
5AE27-038/2022 FNM96-005/2021 MAE27-068/2021 F
6AE27-039/2022 FNM96-005/2021 MAE27-068/2021 F
7AE27-040/2022 FNM96-005/2021 MAE27-068/2021 F
Feuil1


I would also like to ask you for the creation of a 2nd Macro, I will explain the details and why:
When these two empty lines are inserted, it is for a particular need, it is because some of my correspondents wish to receive the file with these two empty lines.
When I sent my file to my correspondents, I need to find it without these inserted lines.
Can you please code me a 2nd macro which removes all these empty lines which have been inserted in the "N:T" columns and find my starting file.
Thank you for your availability.
 
Upvote 0
harzer said:
Thank you for your reply. The code almost suits me because I forgot a small detail (sorry), namely:
In my example, it is true that the number of rows in columns "N:T" is identical, but it sometimes happens that it is greater in column "N", how can you get the largest number of rows in the set of all columns "N:T"
Click to expand...
Did you at least try my code? I think that may return what you want.
On your latest example, this is what it will do:

1705413817447.png
 
Upvote 0
Hello Joe4,
Thanks for your proposition.
I just tested it, it works very well, thank you.
However, the question of determining the large number of lines in columns "N":"T" remains relevant, see the case where the largest number of lines is in column "T":

Permuter2Ranges.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXY
1abcdefghijklmJeunePèreMèreJeunePèreMère
2abcdefghijklmNM96-010/2021 FNM96-002/2020 MNM96-020/2020 FNM96-008/2021 MNM96-002/2020 MNM96-020/2020 Fnopqrstwxyz
3abcdefghijklmAE27-026/2022 FNM96-046/2019 MNM96-036/2021 FAE27-018/2022 MNM96-005/2021 MAE27-068/2021 Fnopqrstwxyz
4abcdefghijklmAE27-037/2022 FNM96-005/2021 MAE27-068/2021 FAE27-019/2022 MNM96-005/2021 MAE27-068/2021 Fnopqrstwxyz
5AE27-019/2022 MNM96-005/2021 MAE27-068/2021 F
6AE27-019/2022 MNM96-005/2021 MAE27-068/2021 F
Feuil1


To read to you.
 
Upvote 0
Could there ever be any data in any other column (outside of columns N:T) that is below the last row with data in columns N:T?
Or will the last row with data in columns N:T always be the last row with data on the whole sheet?
 
Upvote 0
If the data in columns N:T will always be the "lowest" data on the sheet, you could use this version of my code:
VBA Code: 
Sub InsertCells()

    Dim lr As Long
    Dim r As Long
    Dim rng As Range
    
    Application.ScreenUpdating = False

'   Find last row on sheet with data
    lr = Range("A1").SpecialCells(xlLastCell).Row
    
'   Loop through all rows backwards, up to row 3
    For r = lr To 3 Step -1
'       Build range
        Set rng = Range("N" & r & ":T" & r + 1)
'       Insert cells
        rng.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Hello Joe4,
There is no data belonging to other columns located in columns "N:T", therefore, All data located in columns "N:T" belongs to columns "N:T"
The last line must be in the “N:T” column group
 
Upvote 0
harzer said:
Hello Joe4,
There is no data belonging to other columns located in columns "N:T", therefore, All data located in columns "N:T" belongs to columns "N:T"
The last line must be in the “N:T” column group
Click to expand...
No, that is not what I am asking at all.

Let's say that the last row with data in columns N:T is row 100.
Is it possible to have data ELSEWHERE (i.e. in columns A or Y, etc), that would be below row 100 (the last row with data in columns N:T)?
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Search for topics based on their groups.
Replies
7
Views
143
Peter_SSs
Peter_SSs
H
  • Solved
Transpose data.
Replies
4
Views
117
harzer
H
H
  • Solved
Copy the first line and paste it at the start of all subsequent sheets in the same tab
Replies
4
Views
164
harzer
H
H
  • Solved
Find a subject that is in column "P" in columns "G" and "L" and export.
Replies
2
Views
139
harzer
H
H
  • Solved
Modify content of cells in column "F" according to the content of cells in column "A"
Replies
5
Views
342
igold
igold

Forum statistics

Threads
1,215,094
Messages
6,123,071
Members
449,092
Latest member
ipruravindra

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top