Copy Rows

[drewbny]

In excel, you can easily copy a specified amount of columns in between columns.

I'd like to be able to have rows of data already filtered based on criteria, and then be able to insert a certain amount of columns in between all the rows that are currently visible

is there anyway to do that?

 

[Logit]

You can add an additional COLUMN or additional ROW by right clicking on the COLUMN HEADER or the ROW NUMBER then select INSERT.

 

[drewbny]

After i filter one column, i am not allowed to insert rows in between the filtered rows.
I wish i could screenshot my example, but i dont know how to attach an image that is not a hyperlink

 

[Logit]

.
You can upload your image or your workbook to a cloud site like DROPBOX or similar, then provide a download link in your next post.

 

[drewbny]

Hi , i cant install dropbox at work.
I was not clear.

If i have a worksheet, and i filter out data, i may have rows that are not next to each other that are now shown on my screen.

If i want to insert 5 rows above all the visible rows that are remaining, Excel will not allow me to do so.
excel will only let me insert 5 rows above a Row that i manually select. It will not let me insert multiple rows to all the visible rows shown
how can i get excel to let me insert rows to all visible rows shown, based on my filtered selection?

 

[drewbny]

i attached the file.
the file currently has data that is filtered.
how can i insert multiple blank rows above each row that is filtered at the same time? excel will only let you add multiple rows above the highlighted row when it comes to filtered data

https://www.dropbox.com/s/fyegeb7nzuh5e2q/practice file.xlsx?dl=0

Hi , i cant install dropbox at work.
I was not clear.

If i have a worksheet, and i filter out data, i may have rows that are not next to each other that are now shown on my screen.

If i want to insert 5 rows above all the visible rows that are remaining, Excel will not allow me to do so.
excel will only let me insert 5 rows above a Row that i manually select. It will not let me insert multiple rows to all the visible rows shown
how can i get excel to let me insert rows to all visible rows shown, based on my filtered selection?

 

[Logit]

.
[FONT=&quot]Highlight rows 2 - 309[/FONT]


Press F5


Click Special


Click Visible Cells Only


Click OK.


Select row 309 (the last visible filtered row).


Right click and select INSERT ROW.


One blank row inserted ABOVE each filtered / visible row.


Done.
[FONT=&quot]
[/FONT]

 

[drewbny]

thx for your response.
i know i can insert one blank row.
BUt is there a way i can insert multiple blank rows . for instance, id like to add 7 blank rows above each filtered row at the same time
instead of manually adding 1 blank row at a time?

 

[Logit]

.
See if this works.

Instructions:

Place a command button on the sheet within Row 1.
[TABLE="width: 184"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: left"]

Sheet is already filtered.

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD]First click on Col Header T.[/TD]
[/TR]
[TR]
[TD]Then click on button.[/TD]
[/TR]
[TR]
[TD]Enter 373 and OK.[/TD]
[/TR]
[TR]
[TD]Enter 7 and OK.[/TD]
[/TR]
</tbody>[/TABLE]


This macro goes in a routine module :

Sub InsertBlankRows()
    Dim i As Long
    Dim Lastx As Long
    Dim rng As Range
    Dim txt As String
    Dim fndMe As String
    Dim numTimes As Integer
    On Error Resume Next
    
    Application.ScreenUpdating = False
    
    txt = Application.ActiveWindow.RangeSelection.Address
    Set rng = Application.InputBox("Select column with specific text:", "Column Select", txt, , , , , 8)
    
    If rng Is Nothing Then Exit Sub
    
    If (rng.Columns.Count > 1) Then
        MsgBox "You must select one column only !", , "Column Select Error"
        Exit Sub
    End If
    
    fndMe = InputBox("Enter Filtered Term", "Which Term ?")
    numTimes = InputBox("Enter Number Empty Rows To Insert :", "How many rows ?")
    Lastx = Cells(Rows.Count, rng.Column).End(xlUp).Row
    
    For numTimes = 1 To numTimes
        For i = Lastx To 1 Step -1
          If InStr(1, rng.Cells(i, 1).Value, fndMe) > 0 Then
            Rows(rng.Cells(i, 7).Row).Insert shift:=xlDown
          End If
        Next
    Next
    
    Application.ScreenUpdating = True
End Sub