Free Microsoft Excel 2013 Quick Reference

Macro Find text and then insert row

Hey Excel Wizards,

I am a complete novice and I need your help. I am attempting to find a certain text in a column (i.e. find "http" in "") and then insert a row above the cell and move on to find the next "http" in the column. Here is what I have so far:

Sub Macro3()
' Macro3 Macro
    Dim ResultRange As Range
    Dim ResultCell As Range
    Set ResultRange = Range("A1:A5000")
    For Each ResultCell In ResultRange
        Cells.Find(What:="http", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate

Next ResultCell

End Sub
This is some Frankenstein code and I would sincerely appreciate your help!


Post your answer or comment

comments powered by Disqus
Going round in circles here.... can anyone help please?

I need a macro to do this:

Search down column B for some specific text and then insert a new row
immediately above the found cell. The new row must copy the formulas and
formatting (but not the values) of the row above the found cell.

So, find text "last risk above" in column B - let's say this is cell B40.
Macro then copies the formatting and formulas of B39 to a new row between
B39 and B40.

Thanks in advance,


Hi Bos ? Can u help me again please.
How to Find a string and then insert new row above the SUM Formula ?
I have attached the Example file (Example.xls)

thanks b4

i am trying to write a macro to remove some page headers. i need to find each occurance of some text and then remove the row it is on and the next 8 rows. the macro below works but it finishes with an error and the line highlighted in yellow in Visual Basic is Loop While Not c Is Nothing And c.Address <> firstAddress

With ActiveSheet.UsedRange.Cells

Set c = .Find(What:="Micro Syste", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Selection.Delete Shift:=xlUp

Set c = .FindNext()

Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

i don't fully understand the logic of the macro because i have created it by copying from examples even though i have tried looking things up in the VB help programme. my guess of what the code does are the comments in brown:

With ActiveSheet.UsedRange.Cells tells it to only look at cells with something in

Set c = .Find(What:="Micro Syste", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False) sets a variable called c so where c is found in the code it will look for "Micro Syste" text.
If Not c Is Nothing
firstAddress = c.Address this creates a variable called firstAddress with the cell reference of the text found by c?
c.Select makes the cell found active
Selection.Delete Shift:=xlUp selects 9 rows and deletes them

Set c = .FindNext() resets the variable c to the cell reference of the next occurance of the text

Loop While Not c Is Nothing And c.Address <> firstAddress keeping looping is all i understand here
End If
End With

I have a spreadsheet that I'm trying to run a seach on to find a specific
sting of test. I can run the search but, now I want to ake it one step
further. Is it possible to search for a sting of text and then delete part
of the sting and leave some of the text? Then is it possible to do this a
macro? Thanks for the help.

I have a data input file which is mostly composed of of numerical data.
However, after several rows of numerical data, there is a row of text to separate different test cases.

I want to code a "do until" loop that will look at each cell in column A. I want to include an "if then else" statement that will end the "do until" loop when it reaches a cell with text and then select that same cell with the text. I want it to stop when it reaches a cell with text period. Not just for a specific word or something. I'm not trying to delete the cells with text or anything either. I'm just trying to figure out how to write the "condition" for the "if then else" statement right now.

I am trying to start to automate this painful report that I do by hand
every monday, I kinda was noticing what manual steps I was doing and I
figure I try to automate some of it, maybe all of it with time :- )

So I wanted to do first is.

The excel sheet has 6 columns, A - F


D E and F have time and productivity data, but I only want to sort by A
first and then by B, this I already done, so Sort by Date, then by
Name. Then Look into column B and insert a 25 row space between the
Names if the are not the same..

Here is my VB code so far: the Sorting works (maybe there is a way to
shorten the code), but it works..

The insert 25 rows, doesn't work, so I am figuring there is something
wrong there, that's the part I need help with..

Sub FormatTablebyName()

' This one will sort the column A/B and then seperate by 25 rows column
B if name is not the same.

Dim iRow As Long
Dim lastRow As Long

lastRow = Range("A65536").End(xlUp).Row


' Sort Column A ( Works )

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

' Sort Column B ( Works )

Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

' Seperate Names in Column B with a 25 space row insert

For iRow = 1 To lastRow
If Cells(iRow, 2) <> Cells(iRow + 1, 2) Then
Rows(iRow + 25).Insert
lastRow = lastRow + 25
Row = iRow + 25
End If
Next iRow
End Sub

If I replace the 25 with a 1, it works perfectly for inserting the 1
row insert, but not with 25, what am I doing wrong, help this niewbie
out, thanks!

I wonder if someone could help me write a procedure to perform the following task:
I have a set of data, with a column labeled "Description" I want to step through the data and as I do compare the each row in the Description column. If the information in the cells are a match, step through, if they are not a match then insert a blank row between them. i.e. if cell A1 = "tennis ball" and cell A2 = "tennis ball" so a match is made then excel will automatically step down and do nothing if however cell A2 = "rugby ball" then there is no match and a row is inserted between "tennis ball" and "rugby ball".

I am trying to make a macro that open all the texts files from a specified
directory one by one.

Everytime a txt file is opened i want to search for a specified string (for
ex. PETER)
and if the string exists then all the text before this string must copied to
a empty cell at any colunm (for example c1)

Then the secont text to c2 , c3 e.t.c

lets see an example.

The text file text1.txt has the folowing text.

"sdfgdfg dfgdfgsd fghdfghfgh
fgfghdfgh gfhfghdfgh fghfdgh

fghfdgh gfhdfgh fghfgh 54645645 5464546
gfhfghj vcbncvbn hfgh rthyrtyertyrty
greece summertime dfrdfg errtt retyrty fdgsdfg gh
sdfghfgh dfghdfgh dfghdfgh
fghfhgffghdfhfgh fghfgh dfghdfghdfgh"

I search the string "greece" .
Tha macro finds it and then copies all the text before and up to this string
to the cell a1.

Then comes the secont txt file , then the third etc.


Hi there,

I am looking for a macro to detect and hide blank rows.
I am using two tables in my worksheet.
The second table is only showing a value when the answer on
the if command in that table is positive.
However, I want the results in my second table nicely sorted out
without any blank lines between them.
What would be the best way to hide the empty lines?

Thanks in advance,


Good morning

This is something I always have trouble with and waste a lot of time on!
I have two sheets of data and I need to compare them - to see which rows appear on both sheets and which are new rows or rows that do not appear. It makes sense to see this visually, seeing the worksheets side by side. My current approach is to sort the columns by a value and then insert rows and shift things around on the sheet until the values line up.

There must be a faster way of doing this! how do others go about this?

Hopefully this illustration shows what I am trying to do....
Cheers for any suggestions!

******** ******************** ************************************************************************>Microsoft Excel - Book3___Running: xl2002 XP : OS = Windows XP File Edit View Insert Options Tools Data Window Help AboutL8=
ABCDEFGHIJ1Beginning: End Result 2Wksheet 1Wksheet 1Wksheet 2Wksheet 2 Wksheet 1Wksheet 1Wksheet 2Wksheet 2 36565123412065651234120 6565123412065651234120 46565123414065651237100 65651234140 56565123710065651237120 6565123710065651237100 66565123712065657234100 6565123712065651237120 76565123714065657234120 65651237140 86566723410065657234140 6566723410065657234100 96566723412065657234160 6566723412065657234120 106566723414065658988100 6566723414065657234140 116565898810065658988120 65657234160 1265658988120 6565898810065658988100 1365658988140 6565898812065658988120 1465658988160 65658988140 1565658988180 65658988160 1665658988200 65658988180 17 65658988200 Sheet1
[HtmlMaker light Ver1.11] To see the formula in the cells just click on the cells hyperlink or click the Name box

I am trying to copy A225:Q227 and then "insert copied cells" and "shift cells down", but the option to "insert copied cells" is greyed out. A long long time ago I asked how to prevent copying and pasting, and recall the ability to disable that in the advanced settings, but the stopping of copy and paste is not selected there.

Thoughts? This is quite frustrating. :-(

Hi there,

I am developing a file in excel and now I require to find a cell in a range based on the value in text box and then load the value of the adjacent cell to variable and then delete such row. After loading this variable I want to delete the row in another sheet based on the value of variable. This is very urgent. Please help


Hello -

I'm trying to split text based on the length of the string (70 char) in a column and insert rows directly below the original cell with the remaining string. It should insert as many rows as needed where the length does not exceed 70 char. I've used vba code that JMHANS posted a while back (thank you!!) and converted it to rows instead of columns ~ the post is called "Split text over 255 characters". My problem is this, I cannot seem to loop through an entire column, it only recognizes the first cell in the range (C) specified and either deletes the remaining cells or does nothing with them. I'm beyond new to VBA and macros and I'm hoping the solution is straight forward!

This is a sample of what I've been working with:

A B C 1 X X characters in cell = 1000 2 Y Y characters in cell = 800 3 Z Z characters in cell = 140

Desired result

A B C 1 X X first 70 characters of 1000 2 X X *insert row 2 of 14 # X X +rows+ 15 X X insert row 14 of 14* 16 Y Y first 70 characters of 800 17 Y Y *insert row 2 of 11 # Y Y +rows+ 28 Y Y insert row 11 of 11 29 Z Z first 70 characters of 140 30 Z Z insert row 2 of 2

As you can see from above, i'm repeating the values in columns A and B which is also desired but that was beyond the first step I was focusing on to just get the insert rows to repeat through the list!

This is the modified code that I'm working with:

    Dim Row As String 
    Row = InputBox("Enter the letter of the column that the comments are stored in.") 
    Dim cel As Range 
    For Each cel In Range(CStr(Row & "1" & ":" & Row & "60000")) 
        If Len(cel.Value) > 70 Then 
            newRow = 0 
            Do While Len(cel.Offset(newRow, 0)) > 70 
                temporigval = cel.Offset(newRow, 0).Value 
                cel.Offset(newRow, 0).Value = Left(temporigval, InStrRev(temporigval, " ", 70) - 1) 
                cel.Offset(newRow + 1, 0).Value = Right(temporigval, Len(temporigval) - InStrRev(temporigval, " ", 70)) 
                newRow = newRow + 1 
        End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I did not create this code so if there are questions as to why it was done this way, I don't know! If there is an easier way to do what I'm looking to do, please feel free to post your suggestions! This just happened to be the simplest and most straightforward code I could find!

Thanks in advance!!

Good Morning,

Well i'm officially stating i dislike veyr much find/lookup and other similar functions as i cant ceem to get my head around them enough to create one sonow i turn to you...the experts.

I need a code that when any cell in the 1st row of a column is selected it asks "Is this person no longer employed?" If the answer is no then nothing happens and if the answer is yes then thats where the macro begins. (I can get things to work upuntil here).

1. Finds the last cell in this column containing a value (not X or n/a text).
2. Copies this column to sheetx' next empty column (as there may be prior data).
3.Back to the original sheet and replaces all the cells from row 3 down with the letter X, deletes all comments from this column, and then deletes the text from Row 1 in this column.

Anyone got 10minutes to throw that together?


I need a macro that will select all of my sheets, find the line 2 rows above a cell called Totals in column A of each sheet, copy that line and then insert the copied cell 5 times.

I have a data entry sheet for quotes with 50 or so lines with formulas to calculate the quote based on costs, rebates etc... I then have 4 different presentation sheets for different customer types which reference the data entry sheet. Each sheet in the workbook has the same number of lines. I want to be able to add 5 lines at a time to each sheet while copying the formulas (both the formulas of the data entry sheet and the references in the other sheets).

Thanks in advance to anyone who can help.

can you please help me out with this one:
I have a sheet where I want to find the value "nn" in column D.
Wherever d contains "nn" I want to insert a new row below this row and
then copy the "nn" row to the new row. I have tried a lot of different
versions for myself in VBA but can not seem to get it to work and don't
understand how to set it up to work.


I have a file where it has a list in column A of information.
I need to find the lines with the text "[Permalink]" on the end, I need to remove the text and then insert a line underneath. I've been trying to write the code but am having problems, does anyone know what the code is? I'm pretty new to Macros, I can usually record ok but writing Macros is a bit of a struggle.

Any replies would be appreciated.



can you please help me out with this one:
I have a sheet where I want to find the value "nn" in column D.
Wherever d contains "nn" I want to insert a new row below this row and
then copy the "nn" row to the new row. I have tried a lot of different
versions for myself in VBA but can not seem to get it to work and don't
understand how to set it up to work.


so what I am trying to do is find some text in a cell (I know the text represents the end of a list in a row of text) and then select the row from A:F and then format the selected cells with a line on the bottom.
I have gotten it to run when recording a macro BUT when I apply to other sheets it does not format the way it should.
This is the initial starting code but I want to loop this with the row range also added.
All help appreciated

Sub Sheet_Format()
Cells.Find(What:="ATM CARDS", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
End Sub

I'm working on my PhD dissertation in linguistics, and I have a specific Excel problem that I'm sure there's an easy macro to solve, but I know nothing about them. Hopefully someone can help me out and literally save me months of work (and some well needed sanity!)

I have text from recorded interviews (in Spanish) in column A, one word in each column. What I need to do is find specific words, and then automatically insert text in adjacent columns (linguistic coding for a stats program) For example, say I need to find the word "gente" throughout the worksheet. I need to find it, and then place codes next to it. So I want all instances of "gente" to read:

gente E C N x o c n u Z

Where each of the code letters is in a different column. Ideally, the macro should do this for all instances of gente in the worksheet (maybe 100 or more).

I would GREATLY appreciate any help. And if you ever need Spanish/linguistics help, don't hesitate to ask :-)

BTW, gente=people

Hi, i'm looking for macro that would find text select that row and define a name for that row, for example find text "hello" select that row and define the name for that row "hello". i got to the point where it finds the name and selects that row - how can i define the selected row?

thank you

here is the code i got so far

Application.CommandBars("Stop Recording").Visible = False
Cells.Find(What:="hello", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).EntireRow.Select

I'm trying to create a macro that goes to the last row of data, goes to the
next row and then inserts a row. I then need it to copy & paste from the row
above it (formulas & formats only). Here is what I have so far, but its not

Selection.End(xlDown).Select , Offset(1, 0)

range("NewPartDescr").Offset(-2, 0).EntireRow.Copy
range("NewPartDescr").Offset(-1, 1).EntireRow.Select

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

I am wanting to cut out a row based on if a Date is entered in column A
Take that cut row and insert- copy or append into Workbook 2 Sheet1 .

This will save me the trouble of having to do a sort on the list and then
selecting and cutting out rows.
Then I have to resort the list. This is taking me a bit of time after
doing the functions several times a day..


Hi, I've searched through several forums for the answer to my problem but as yet not been successful. Can anyone help please?

I'd like to write a loop macro capable of searching for text in a column of data. On finding the word (which will be part of several words in cell) I want to copy that whole cell and paste into another sheet forming a new list of data.

The text I'm looking for is the word "TOTAL " ie the word is always followed by a blank space and not just "TOTAL" without a space. As I said before it will feature in a cell with more than one word ie "TOTAL ENERGY" or "TOTAL SPEND" and not "TOTAL" on its own.

The range of the data to be searched is confined to one column but as many as 5000 rows to loop through. The data my have blank cells, ie it is not a continuous list of non blank cells to be searched. It is important that the loop macro picks out all instances and copies to a new list of data with no blank rows in another sheet.

Hope that all this makes sense and I'd be amazingly grateful for any help. Thanks J

No luck finding an answer? You could always try Google.