Insert a blank row after based upon a condition

[mm82]

Hi all,

I am quite new with regards to excel marcos and VBA. I am trying to build a macro that adds a new line below a column contains a specific value.

For example when column J contains "615006" I would like to add a line below that line and if possible also copy the entire previous line but replaced the value in column J with value "615006".

See attached file for more details.

Mikael

[sunflowers]

Try this :

Sub InsRow()
Dim i As Long
For i = 21 To 1 Step -1
    If Cells(i, 10).Value = 615006 Then
        Cells(i, 10).EntireRow.Insert
    End If
Next
End Sub  

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

Note: Your sample seems to insert a line above?

[mm82]

Thanks, this one did the trick:

Sub mm82()
Dim i As Long
For i = Range("J" & Rows.count).End(3)(1).Row To 2 Step -1
If Range("J" & i).Value = 615006 Then
Range("J" & i + 1).EntireRow.Insert
Range("J" & i + 1).Value = Range("J" & i).Value
End If
Next i
End Sub

A couple of more questions:

when a new line is added I want to that the new line should have colmn J equal for example xxx and all other colmn values copied from the previous line, is that also possible?

is it possible to dynamically configure this so that I get a dialog that asks me to select which column to search by marking that column? and also what to search for (in my case 615006) and what the new should be in that column? in total three dialogs?

[mm82]

I have about 30000 lines that I need search, is that possible as well?

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

Not sure what this means "what the new should be in that column?". So I may have misunderstood.

[mm82]

You understood me correctley!

How do I change it so I just mark the column in the spread sheet that I want to search in instead of typing it? Also when I search for example the values in a column they are "spaces" after.. so if I search for "615006" I will not get a hit, but if I enter "615000 " I get a hit?

The last one, when I copy your marcro and save it the sheet as *xlsm the macro is not avaliable the next time I open the file??

[JOHN H. DAVIS]

You would need to select the column first.

Please Login or Register  to view this content.

"The last one, when I copy your marcro and save it the sheet as *xlsm the macro is not avaliable the next time I open the file??"

I don't know what's going on there. I only have 2003. Perhaps create an xlsm file and then copy and paste? I dunno.

[mm82]

Thank you for all the help john! Made my day

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.


9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

[HaHoBe]

Hi, mm82,

your post #4 does not comply with our Forum Rules #3 - you should wrap your procedure with code tags.

Ciao,
Holger