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

 

[chua_sb]

You too!

Note that I updated the post marked as the solution. You typically want to mark the post that contains the solution.
That way if someone were to view the thread, they would see the original question, and then the solution right beneath it.

ok ok ok. I will do that sir.

 

[chua_sb]

Joe.. I have one more issue i just notice.. Once the cell detect ZERO. it will have error message.. Any chance you can add in a code that don't count zero?

 

[chua_sb]

Really one last question.. What code do i need to check if i want to fix a column for the code?

 

[Joe4]

Joe.. I have one more issue i just notice.. Once the cell detect ZERO. it will have error message.. Any chance you can add in a code that don't count zero?

Change this line:

            Cells(r + 1, c).Resize(n).EntireRow.Insert Shift:=xlDown

to this:

            If n > 0 Then Cells(r + 1, c).Resize(n).EntireRow.Insert Shift:=xlDown

Really one last question.. What code do i need to check if i want to fix a column for the code?

What do you mean?
Are you saying that you always want to loop through a specific column?
If so, then change this:

    c = ActiveCell.Column

to the column NUMBER you want it to run against, i.e. "A"=1, "B"=2, "C"=3, etc.

   c = 10

 

[chua_sb]

You are truly amazing.. THank you so much.. Do you actually sleep.. LOL

 

[Joe4]

You are truly amazing.. THank you so much.. Do you actually sleep.. LOL

You are welcome.
Yes, I actually slept in between the time of my last two replies!

 

[chua_sb]

Or maybe you coding in your sleep too. LOL.

You have a good day ahead.

 

[chua_sb]

HI Guru,
One more help.. Say, if the value, of the cell is "3" and i want the 3 -1 = 2, how do i do the code?

 

[Joe4]

HI Guru,
One more help.. Say, if the value, of the cell is "3" and i want the 3 -1 = 2, how do i do the code?

That appears to be a whole new different question, and as such, should be posted to a new thread.

 

[chua_sb]

Ok Ok.. Thanks Guri