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

[harzer]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	a	b	c	d	e	f	g	h	i	j	k	l	m	Jeune	Père	Mère		Jeune	Père	Mère
2	a	b	c	d	e	f	g	h	i	j	k	l	m	NM96-010/2021 F	NM96-002/2020 M	NM96-020/2020 F		NM96-008/2021 M	NM96-002/2020 M	NM96-020/2020 F	n	o	p	q
3	a	b	c	d	e	f	g	h	i	j	k	l	m	AE27-026/2022 F	NM96-046/2019 M	NM96-036/2021 F		AE27-018/2022 M	NM96-005/2021 M	AE27-068/2021 F	n	o	p	q
4	a	b	c	d	e	f	g	h	i	j	k	l	m	AE27-037/2022 F	NM96-005/2021 M	AE27-068/2021 F		AE27-019/2022 M	NM96-005/2021 M	AE27-068/2021 F	n	o	p	q
Feuil1

Result after inserting the two empty lines

Permuter2Ranges.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	a	b	c	d	e	f	g	h	i	j	k	l	m	Jeune	Père	Mère		Jeune	Père	Mère
2	a	b	c	d	e	f	g	h	i	j	k	l	m	NM96-010/2021 F	NM96-002/2020 M	NM96-020/2020 F		NM96-008/2021 M	NM96-002/2020 M	NM96-020/2020 F	n	o	p	q	rstwxyz
3	a	b	c	d	e	f	g	h	i	j	k	l	m								n	o	p	q	rstwxyz
4	a	b	c	d	e	f	g	h	i	j	k	l	m								n	o	p	q	rstwxyz
5														AE27-026/2022 F	NM96-046/2019 M	NM96-036/2021 F		AE27-018/2022 M	NM96-005/2021 M	AE27-068/2021 F
6
7
8														AE27-037/2022 F	NM96-005/2021 M	AE27-068/2021 F		AE27-019/2022 M	NM96-005/2021 M	AE27-068/2021 F
9
10
11
Feuil1

Thank you in advance for your contributions.

 

[harzer]

Hello Joe4,
In the case where the last line must be in column "T", the final result after inserting the two empty lines should look like this.

Permuter2Ranges.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	a	b	c	d	e	f	g	h	i	j	k	l	m	Jeune	Père	Mère		Jeune	Père	Mère
2	a	b	c	d	e	f	g	h	i	j	k	l	m	NM96-010/2021 F	NM96-002/2020 M	NM96-020/2020 F		NM96-008/2021 M	NM96-002/2020 M	NM96-020/2020 F	n	o	p	q
3	a	b	c	d	e	f	g	h	i	j	k	l	m								n	o	p	q
4	a	b	c	d	e	f	g	h	i	j	k	l	m								n	o	p	q
5														AE27-026/2022 F	NM96-046/2019 M	NM96-036/2021 F		AE27-018/2022 M	NM96-005/2021 M	AE27-068/2021 F
6
7
8														AE27-037/2022 F	NM96-005/2021 M	AE27-068/2021 F		AE27-019/2022 M	NM96-005/2021 M	AE27-068/2021 F
9
10
11																		AE27-019/2022 M	NM96-005/2021 M	AE27-068/2021 F
12
13
14																		AE27-019/2022 M	NM96-005/2021 M	AE27-068/2021 F
15
16
17
Feuil1

 

[Joe4]

I understand that, but once again, that is NOT what I am asking.

Please answer this question I asked in my last post.
Let's say that the last row with data in columns N:T is row 100. (it does not matter to me whether it is column N or column T, I can account for that).
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)?

What I REALLY need to know is the data in the OTHER columns outside of columns N:T.
Whatever the last row with data in columns N:T is (it doesn't matter to me which of these 7 columns in may come from), is it ever possible to have data below this row in the non N:T columns (like, A-M, U-Y, etc)?

PLEASE ANSWER THIS QUESTION, as it will determine the method I use to do what you want.

 

[harzer]

If you assume that the last row in columns "N:T" is 100, then I can tell you that the data in the other columns is much larger than 100, if that's what you want to know.

 

[harzer]

My answer is No.

 

[Joe4]

Whatever the last row with data in columns N:T is (it doesn't matter to me which of these 7 columns in may come from), is it ever possible to have data below this row in the non N:T columns (like, A-M, U-Y, etc)?

If you assume that the last row in columns "N:T" is 100, then I can tell you that the data in the other columns is much larger than 100, if that's what you want to know.

My answer is No.

Your answers seem to be contradictory to each other. If the row number of the last data in the other columns can be much larger than the row number with the last data in columns N:T, then the answer would be "Yes", not "No". So which is it?

 

[Joe4]

Since I cannot seem to get a clear answer, I came up with code that should work either way.

Sub InsertCells()

    Dim lr As Long
    Dim r As Long
    Dim rng1 As Range
    Dim rng2 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 to look for values (N:T)
        Set rng1 = Range("N" & r & ":T" & r)
'       See if any values in columns N:T
        If Application.WorksheetFunction.CountBlank(rng1) < 7 Then
'           Build range to insert
            Set rng2 = Range("N" & r & ":T" & r + 1)
'           Insert cells
            rng2.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[Joe4]

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.

If these cells in columns N:T are still empty, here is code that will reverse the other code, and delete those newly added cells once again:

Sub RemoveCells()

    Dim lr As Long
    Dim r As Long
    Dim rng1 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 to look for values (N:T)
        Set rng1 = Range("N" & r & ":T" & r)
'       See if all cells in columns N:T are blank
        If Application.WorksheetFunction.CountBlank(rng1) = 7 Then
'           Delete cells and move cells below up
            rng1.Delete Shift:=xlUp
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[harzer]

Hello Joe4,
I took your very first code and took the liberty of modifying a line in your code.
Following this, your code works very well and gives the desired result according to the 3 scenarios:
first case: If the last line is in column "N" --> the code works fine.
Second case: If the last line is the same in column "N" and in column "T" --> the code works well too.
Third case: If the last line is in column "T" --> the code works well too.
To read to you.

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        This is the line I modified
     lr = Sheets("Feuil1").Range("N:T").Find("*", , , , xlByRows, xlPrevious).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

 

[MARK858]

@harzer if you want the last row in multiple contiguous columns with data try...

lr = Columns("N:T").Find("*", , xlValues, , xlByRows, xlPrevious).Row

 

[harzer]

Hello Joe4, Peter_SSs & MARK858,
Thanks to MARK858 for his suggestion to determine the last row in several contiguous columns.
Thanks also to Peter_SSs for his suggestion, it is fast and works very well.
A special thank you to joe4 for his patience and tenacity in continuing to find me a solution, especially with a vba novice like me.
Thanks also for the code that allows me to remove the added empty lines.
Salutations to all.