I want to use code to insert a row above a row where the first value found
in column D equals "G" I do not want to insert an empty row for each "G"
found.

Perhaps the code is similar to code I am already using where rows are
deleted. Any help is appreciated.
Pat

Private Sub DeleteRows_Click()
Dim X As Long
Dim Y As Long
Y = Range("A65536").End(xlUp).Row
'y= find the last non empty cell in column A
For X = Y To 1 Step -1
'Lcase gets the lower case of the word
If LCase(Cells(X, 1).Value) = "s" Then
Rows(X).Delete

End If
Next X

End Sub

Dim rng as Range, lastrow as Long
lastrow = cells(rows.count,4).End(xlup).row
set rng = Range("D1")
do while ucase(rng) < "G" and rng.Row <= lastrow
rng = rng.offset(1,0)
Loop
if Ucase(rng.Value) = "G" then
rng.Entirerow.Insert
End if

this will insert a row above the first G found in column D. Is that what
you want?

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
I want to use code to insert a row above a row where the first value found
in column D equals "G" I do not want to insert an empty row for each "G"
found.

Perhaps the code is similar to code I am already using where rows are
deleted. Any help is appreciated.
Pat

Private Sub DeleteRows_Click()
Dim X As Long
Dim Y As Long
Y = Range("A65536").End(xlUp).Row
'y= find the last non empty cell in column A
For X = Y To 1 Step -1
'Lcase gets the lower case of the word
If LCase(Cells(X, 1).Value) = "s" Then
Rows(X).Delete

End If
Next X

End Sub

Hi, Tom

I have tried your code but it hangs once the commandbutton is clicked. Excel
has to be closed via Ctl-Alt-Del. What could be happening?
Pat


"Tom Ogilvy" wrote in message
...
Dim rng as Range, lastrow as Long
lastrow = cells(rows.count,4).End(xlup).row
set rng = Range("D1")
do while ucase(rng) < "G" and rng.Row <= lastrow
rng = rng.offset(1,0)
Loop
if Ucase(rng.Value) = "G" then
rng.Entirerow.Insert
End if

this will insert a row above the first G found in column D. Is that what
you want?

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
I want to use code to insert a row above a row where the first value
found
in column D equals "G" I do not want to insert an empty row for each "G"
found.

Perhaps the code is similar to code I am already using where rows are
deleted. Any help is appreciated.
Pat

Private Sub DeleteRows_Click()
Dim X As Long
Dim Y As Long
Y = Range("A65536").End(xlUp).Row
'y= find the last non empty cell in column A
For X = Y To 1 Step -1
'Lcase gets the lower case of the word
If LCase(Cells(X, 1).Value) = "s" Then
Rows(X).Delete

End If
Next X

End Sub