473,375 Members | 1,292 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,375 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 1221

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

Similar topics

by: aerobar2 | last post by:
When I debug my application I get "no symbols loaded" ================= 'DefaultDomain': Loaded 'c:\winnt\microsoft.net\framework\v1.1.4322\mscorlib.dll', No symbols loaded. 'MHSFire': Loaded...
by: Grey | last post by:
i use "this.Page.RegisterStartupScript()" to display the client-side script of confirmation box ...but I want to know that how can I know the user was clicked "YES" or "NO" as I need to do different...
by: EdWhyatt | last post by:
Hi all, I have searched the group with no answer to this particular problem. In my sendmail program, I would like to have the ability to send a mail message with no-one email address in the To...
by: Phil H | last post by:
I have a VB.NET console application that uses the Excel PIA. I set it up to run on a W2K box through Task Scheduler, under a domain account that has rights to all of the resources the app uses. I...
by: comp.lang.tcl | last post by:
set php {<? print_r("Hello World"); ?>} puts $php; # PRINTS OUT <? print_r("Hello World"); ?> puts When I try this within TCL I get the following error:
by: Joe Peterson | last post by:
I've been doing a lot of searching on the topic of one of Python's more disturbing issues (at least to me): the fact that if a __del__ finalizer is defined and a cyclic (circular) reference is...
by: balakrishnan.dinesh | last post by:
hi Frnds, I need Confirm box with "yes" or "no" buttons, Is that possible in JAVASCRIPT , Can anyone tell me the solution for this or anyother way to create confirm box with "yes" or "no" button?...
by: RSbroman | last post by:
I have created an append query that I would like to run when the DB is opened. However I do not want the end user to receive the "yes" "no" confirmation box when the query is being run. Can...
by: senthilkumarb | last post by:
Hi every body, i need a snippet in JavaScript to perform close window(browser) when we close window it should give alert with "YES" & "NO" button if yes the window should stay still otherwise...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.