Insert row on base on cell value

[chua_sb]

Hi Eveyone and Expert.
I need help.. I want to insert row base on the cell value. Can anyone help me out here?

Sub BlankLine()
'Updateby20150203
Dim Rng As Range
Dim WorkRng As Range
Dim xInsertNum As Long

' On Error Resume Next
xTitleId = "Kutools for Excel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
If WorkRng Is Nothing Then Exit Sub
xInsertNum = Application.InputBox("The number of blank rows you want to insert", xTitleId, Type:=1)
If xInsertNum = False Then
MsgBox " The number of blank rows you want to insert ", vbInformation, xTitleId
Exit Sub
End If
Set WorkRng = WorkRng.Columns(1)
xLastRow = WorkRng.Rows.Count
Application.ScreenUpdating = False
For xRowIndex = xLastRow To 1 Step -1
Set Rng = WorkRng.Range("A" & xRowIndex)


If InStr(Rng.Value, "ALLEN") = 0 And InStr(Rng.Value, "ATC") = 0 And IsNumeric(Rng.Value) And Rng.Value > 0 Then
Rng.Offset(1, 0).Resize(xInsertNum).EntireRow.Insert Shift:=xlDown


End If
Next
Application.ScreenUpdating = True
End Sub

 

[Joe4]

Welcome to the Board!

I think we need a little more detail to better understand how you want this to work (i.e. where these rows are being inserted, etc).
Can you just explain, in plain English, exactly how you want this to work?

In these cases, especially if things are data dependent, it is often very helpful to show an example of your data, walk us through an example, and show us what the expected result should look like.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[chua_sb]

Thank you.. I have upload the file.. Basically, when I run the macro, I would VBA to go through the table and insert row based on cell value.. Anything that it's not number, i want to VBA to skip and loop to another cell.. The VBA above will identify the "numbers" in the cell but doesn't insert the row accordingly to the cell value. Can help?

 

[Joe4]

OK, a few questions:

1. Are we just looking at this "Allen ATC" column? Which column letter is this located in on your worksheet?
2. Which cells are we running it against at run-time? Do you want to specify just which cells to run it on, or do you want to loop through every row in the column every time?
3. Where exactly do you want to insert the rows? Just below the row with the number in it, or at the bottom of the sheet?
4. Are these new rows being inserted to be pre-populated with any values? If so, what is being filled in (and from where do these values comes from)?

 

[chua_sb]

Thank you.. I have upload the file.. Basically, when I run the macro, I would VBA to go through the table and insert row based on cell value.. Anything that it's not number, i want to VBA to skip and loop to another cell.. The VBA above will identify the "numbers" in the cell but doesn't insert the row accordingly to the cell value. Can help?

 

[chua_sb]

OK, a few questions:

1. Are we just looking at this "Allen ATC" column? Which column letter is this located in on your worksheet?
2. Which cells are we running it against at run-time? Do you want to specify just which cells to run it on, or do you want to loop through every row in the column every time?
3. Where exactly do you want to insert the rows? Just below the row with the number in it, or at the bottom of the sheet?
4. Are these new rows being inserted to be pre-populated with any values? If so, what is being filled in (and from where do these values comes from)?

Sorry, not sure you see my reply ;

1- The code i attached earlier allow me to choose the column. It's a column "P".. Do you thin it's possible that i can choose the column where i want. Cause the rules are the same which i only want to insert row with numbers
2- I only want to run with the cell in the column i choose. Ie : if i choose column P in the table, i want the VBA to loop and search for number in the cell then insert row base on the cell value.. I want it to loop till the end of the table.
3- I want to insert below the rows
4- I'm not sure but the VBA which i attached, the formula are copied and auto populated.

Sorry as i'm new to VBA. Thank you very much.

 

[Joe4]

Is this actually a table in Excel, or just a list with filters?
The reason why I ask is because if it is a Table, formulas are usually entered automatically when inserting new records in the table, and there is no reason to copy them.
You only want the formulas copied over, right? Not any of the hard-coded data.

Note that you cannot attach files on this site. If you wanted to share a file, you would need to upload it to a file sharing site, and provide a link to it here.
However, if you are able to use the tool I mentioned in my first reply, you will be able to post a data sample that we will be able to copy and use on our side.

 

[chua_sb]

Is this actually a table in Excel, or just a list with filters?
The reason why I ask is because if it is a Table, formulas are usually entered automatically when inserting new records in the table, and there is no reason to copy them.
You only want the formulas copied over, right? Not any of the hard-coded data.

Note that you cannot attach files on this site. If you wanted to share a file, you would need to upload it to a file sharing site, and provide a link to it here.
However, if you are able to use the tool I mentioned in my first reply, you will be able to post a data sample that we will be able to copy and use on our side.

Good Morning Sir
You are correct.. it's a table in excel.. So, no hard coded on the data.. Just need to insert row..

 

[chua_sb]

Good Morning Sir
You are correct.. it's a table in excel.. So, no hard coded on the data.. Just need to insert row..

Hi Joe,
Would it be possible to base on the code that i provided to amend OR insert need code?

 

[Joe4]

Would it be possible to base on the code that i provided to amend OR insert need code?

I typically do not like to try to amend/change code that I didn't write, especially if it is not working in the first place!
If it was working except for one minor piece, or it was working and you wanted to add a little something extra, that would be one thing.
But I don't want to start working with code that is not working and may be far down the wrong path. I prefer to write my own code from scratch, rather than try to deal with that.

Regarding the column to run this against, do you want a prompt to ask for the column letter, or do you just want it to run on the column of the active cell that is selected when you call the macro?

Also, rather than me trying to recreate that table from scratch, can you post it in a manner which allows me to copy it?
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.