471,873 Members | 2,041 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,873 software developers and data experts.

Insert Blank row in Excel if entry in column O = "NO"

I am trying to insert a new blank row above any row in my excel spreadsheet that has the text "NO" in column O. The macro I have written (below) works fine - apart from when there are two or three NO's in consecutive rows - the macro will then insert either two or three rows above the first NO and not insert a row above the next NO's. Any ideas - it's driving me mad! thanks

Sub macro4()

Dim MyRangenew As Range, DelRangenew As Range, Fnew As Range
Dim MatchStringnew As String, SearchColumnnew As String, ActiveColumnnew As String
Dim FirstAddressnew As String, NullChecknew As String
Dim AFnew

Set MyRangenew = Columns("O")
Set Fnew = MyRangenew.Find(What:="NO", after:=MyRangenew.Cells(1), LookIn:=xlValues, Lookat:=xlPart)
If Not Fnew Is Nothing Then
Set DelRangenew = Fnew
FirstAddressnew = Fnew.Address
Set Fnew = MyRangenew.FindNext(Fnew)
Set DelRangenew = Union(DelRangenew, Fnew)
Loop While FirstAddressnew <> Fnew.Address
End If

'If there are valid matches then delete the rows
'f Not DelRangenew Is Nothing Then DelRangenew.EntireRow.Insert Shift:=xlDown

Application.ScreenUpdating = True
Aug 23 '07 #1
0 1164

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by aerobar2 | last post: by
9 posts views Thread by EdWhyatt | last post: by
5 posts views Thread by balakrishnan.dinesh | last post: by
reply views Thread by YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.