inserting row above when cell contains certain text

ctrl+alt_del

New Member
Joined
Nov 21, 2005
Messages
4
Trying to create a macro to insert a row above any cell that contains the word ' OPEN '. Any help would be appreciated. Thanks.
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,928
Welcome to the Board! How about this:

Code:
Sub FindAll()
Dim c As Range

    With Selection
        Set c = .Find(What:="open", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
        If Not c Is Nothing Then
            firstAddress = c.Offset(1, 0).Address
            Do While Not c Is Nothing And c.Address <> firstAddress
                c.EntireRow.Insert
                Set c = .FindNext(c)
            Loop
        End If
    End With
End Sub
 

ctrl+alt_del

New Member
Joined
Nov 21, 2005
Messages
4
thanks much, btadams

I should explain that the cells will contain other text, in addition to " Open ". I ran the macro, and it inserted no rows, even though there are hundreds of cells in the 1800+ rows which contain the word " Open ". Does the line:

Set c = .Find(what:="open", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)

need to be modified because the cells will have more words than just " Open " in them?

Thanks again!
 

ctrl+alt_del

New Member
Joined
Nov 21, 2005
Messages
4
It still did not add any empty rows. Here is an example of the data:
I have over 1800 rows, so a find and manual insert is going to be a pain. Also, some addresses have more rows of data than others, even though that isn't shown in the sample, so I can't just tell the macro add a blank row every X lines.

Thanks yet again....

XYZ Electrical Open
XYZ Electrical, ABC
P.O. Box 387
townname, ST 13245
Persons name
ABCDEFGHIJK Open
ABCDEFGHIJK, Inc.
1234-5 Street Name
townname, ST 12345
Persons Name
QWERTY Co. Open
QWERTY Co.
123 North Street
Townname, ST 12345
Persons Name
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,928
I pasted your example into a worksheet and it inserted empty rows, in fact it got stuck in an endless loop because I hadn't thought about "open" being in the first cell of the selection. Try this:

Code:
Sub FindAll()
Dim c As Range

    With Selection
        Set c = .Find(What:="open", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows)
        If Not c Is Nothing Then
            firstAddress = c.Offset(1, 0).Address
            Do While Not c Is Nothing And c.Address <> firstAddress
                If c.Address = "$A$1" Then firstAddress = Range(firstAddress).Offset(1, 0).Address
                c.EntireRow.Insert
                Set c = .FindNext(c)
                MsgBox c.Address
            Loop
        End If
    End With
End Sub
 

ctrl+alt_del

New Member
Joined
Nov 21, 2005
Messages
4
btadams, your last solution did work. My problem was I misinterpreted your lower case L as a Bar | when I read your updated macro off the the web page, and entered it into the VBA screen. I verified all the characters were correct, and it ran perfectly. Much appreciated!

(of course, now the ditz that asked for this in the first place tells me she gave me the wrong data, so I'll need to see if this macro will apply to the updated worksheet she's sending me.... !@#$%!!!!!)
 

Forum statistics

Threads
1,078,428
Messages
5,340,181
Members
399,358
Latest member
forumas

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top