Counting of Rows and Adding Rows

austinandrei

Board Regular
Joined
Jun 7, 2014
Messages
117
Hi,
I have a data in Column A as below:
Serial Number
899816
899688
899559
899559
899559
895704

<colgroup><col></colgroup><tbody>
</tbody>
If I filter the Serial Number as "899559" then I get 3 items which I would like to copy in a sheet.
Problem is that it is a fixed template and I need to insert it in between.
Thus, since there are 3 items, I would need to insert 3 entire blank rows.
Can you help me to know the macro codes in counting of the number of rows filtered and the macro code to insert that number of rows?
Thanks.


austin
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi,
I have a data in Column A as below:
Serial Number
899816
899688
899559
899559
899559
895704

<tbody>
</tbody>
If I filter the Serial Number as "899559" then I get 3 items which I would like to copy in a sheet.
Problem is that it is a fixed template and I need to insert it in between.
Thus, since there are 3 items, I would need to insert 3 entire blank rows.
Can you help me to know the macro codes in counting of the number of rows filtered and the macro code to insert that number of rows?
Thanks.
austin


Hi, when you filter you only get the three observations correct?

Code:
Sub rowcountInsert()
Sheets("Sheet1").Select
FirstRows = 3[COLOR=#0000ff] 'Starts selection from Row 3[/COLOR]
Lr = Cells(Rows.Count, 1).End(xlUp).Row [COLOR=#0000ff]'Finds last row of data, given that your filter only shows "899599"
[/COLOR]

RowsToMove = Lr - FirstRows


    Rows(FirstRows & ":" & Lr).Select
    Selection.Copy
    Sheets("Sheet2").Select
             [COLOR=#0000ff] 'Is there any specific row you would like to insert it? if so, try next.[/COLOR]
    InsertRow = Sheets("Sheet2").Cells.Find("Find a row where this search is registered").Row
    Rows(InsertRow & ":" & InsertRow).Select [COLOR=#0000ff]'Rows("3:3").Select This will select Row 3[/COLOR]
    Selection.Insert Shift:=xlDown[COLOR=#0000ff] 'Will paste your selection ABOVE what you searched for[/COLOR]
    
        Application.CutCopyMode = False
End Sub

This is a simple one I put together, I can explain further what is happening if needed.
 
Last edited:
Upvote 0
Hi Arithos,
I am not sure what do you mean by "3 observations"?
The number of items is not fix so the count may range from 1 to anything which will then be use as the number of rows to be inserted.
Yes, it will be inserted in Sheet 2 and it will start at A15.
So from the example, it will insert 3 rows from A15.
 
Upvote 0
Hi Arithos,
I am not sure what do you mean by "3 observations"?
The number of items is not fix so the count may range from 1 to anything which will then be use as the number of rows to be inserted.
Yes, it will be inserted in Sheet 2 and it will start at A15.
So from the example, it will insert 3 rows from A15.

In your example. "If I filter the Serial Number as "899559" then I get 3 items which I would like to copy in a sheet."
My macro is dynamic, so if your filtering returns how many rows as you want, it will capture that.

And in Sheet one, in column A, what row is below the "filter cell". that +1 should be the FirstRow, from my macro.
and you can change

Code:
[COLOR=#333333]Rows(InsertRow & ":" & InsertRow).Select [/COLOR][COLOR=#0000ff]'Rows("3:3").Select[/COLOR]

To simply

Code:
[COLOR=#ff0000]Rows("15:15").Select[/COLOR]
This will insert rows at row 15 in sheet 2. You might want to change it to 16, since it inserts above, you can just try it out, and reffine it till it suits your needs
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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