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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
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:

austinandrei

Board Regular
Joined
Jun 7, 2014
Messages
117
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.
 

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,809
Messages
5,598,202
Members
414,218
Latest member
speedbit

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
Top