469,090 Members | 1,296 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

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 1093

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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.