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

H

harzer

Board Regular
Joined
Dec 15, 2021
Messages
148
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.
 
Sort by date Sort by votes
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
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
3abcdefghijklmnopq
4abcdefghijklmnopq
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
11AE27-019/2022 MNM96-005/2021 MAE27-068/2021 F
12
13
14AE27-019/2022 MNM96-005/2021 MAE27-068/2021 F
15
16
17
Feuil1
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0
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.
 
Upvote 0
Joe4 said:
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)?
Click to expand...

harzer said:
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.
Click to expand...

harzer said:
My answer is No.
Click to expand...

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?
 
Upvote 0
Since I cannot seem to get a clear answer, I came up with code that should work either way.
VBA Code: 
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
 
Upvote 0
harzer said:
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.
Click to expand...

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:
VBA Code: 
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
 
Upvote 0
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.

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        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
 
Upvote 0
@harzer if you want the last row in multiple contiguous columns with data try...
VBA Code: 
lr = Columns("N:T").Find("*", , xlValues, , xlByRows, xlPrevious).Row
 
Last edited:
Upvote 0
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.
 
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
387
Peter_SSs
Peter_SSs
H
  • Solved
Transpose data.
Replies
4
Views
337
harzer
H
H
  • Solved
Capitalize the last character in column “K” conditionally.
Replies
3
Views
215
Fluff
Fluff
H
  • Question Question
copy some columns based on the value in column "A".
Replies
9
Views
342
Cubist
Cubist
H
  • Solved
Copy rows if cells in column "K" end with either "X" or "x".
Replies
3
Views
213
rollis13
rollis13

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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