inserting a row based on a condition.

G

Gary

i need a macro to do the following.

if value in cell is >=x then insert a row above.

Possible?
 
R

Richard Buttrey

i need a macro to do the following.

if value in cell is >=x then insert a row above.

Possible?
Click to expand...


Yes, but you need to give some more details as to how this is intended
to operate.

i.e. Are you referring to just a single cell such that when its value
changes to be greater than x, the row is inserted, or are you trying
to loop down a column of cells and wanting to insert several rows if
the condition is met?

If the former, do you want this to kick in once only, or every time x
is changed so that a row might be inserted several times if the
condition is met

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
G

Gary

Sorry for not being clear Richard. What I want is whenever the in any cell
in column A is >=x, the macro should insert a row right above that row.

For example - if i enter x in A14, the macro should insert a row above. now
A14 will automatically become A15.

thanks in advance.
 
R

Richard Buttrey

Put the following in the worksheet_change event procedure



Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target = "x" Then
Target.EntireRow.Insert
End If
End If
End Sub


I wasn't quite sure if you really meant >= x or just >x

The above will insert a row only if you enter an 'x' in column A.
If you really do mean >= x, i.e a row is inserted if you enter x, y or
z then just change the ="x" to >="x"

HTH




Sorry for not being clear Richard. What I want is whenever the in any cell
in column A is >=x, the macro should insert a row right above that row.

For example - if i enter x in A14, the macro should insert a row above. now
A14 will automatically become A15.

thanks in advance.
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Top