Search 12345 and insert row above

donlincolnmre2

Board Regular
Joined
Dec 23, 2006
Messages
142
Hello

I'm looking for a macro that will search the whole sheet for 12345 and if found then insert a row above and position the cursor in the Cell A of that inserted row, any help would be appreciated.

Thanks.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
I'm looking for a macro that will search the whole sheet for 12345 and if found then insert a row above and position the cursor in the Cell A of that inserted row, any help would be appreciated.
Will 12345 only exist once on the worksheet? If not, please explain about what should be selected.
 
Upvote 0
Yes only one time in the whole sheet.

Okay, give this macro a try...
Code:
Sub Find12345InsertRowAboveSelectColumnA()
  Dim Rw As Long
  On Error GoTo No12345
  Rw = ActiveSheet.UsedRange.Find(12345, , xlValues, xlWhole, , , , , False).Row
  Rows(Rw).Insert
  Cells(Rw, "A").Select
  Exit Sub
No12345:
  MsgBox "12345 could not be found!", vbCritical
End Sub
 
Upvote 0
Hello

I'm using Excel 2000 and below is the dummy data, when i run the macro its says not found.

columns

A B C

C 134123 1243
C 134123 1243
C 134123 1243
C 134123 1243
C 134123 1243
C 134123 1243
12345
C 134123 1243
C
C
 
Upvote 0
I'm using Excel 2000...
I don't think this should matter for your current question, but you should mention this fact wherever you ask a question on this (or any other) forum given that your version of Excel is nearly 20 years old and a lot has changed in Excel since then.



...and below is the dummy data, when i run the macro its says not found.

columns

A B C

C 134123 1243
C 134123 1243
C 134123 1243
C 134123 1243
C 134123 1243
C 134123 1243
12345
C 134123 1243
C
C
I cannot tell from your post what cell the 12345 is in, but are you sure that is all of the text in the cell (I am thinking you might have hidden spaces or non-breaking spaces in it)? Lets assume the 12345 is in cell A7 (correct the address if that guessed is wrong), put this formula in an empty cell and tell me what it returns...

=LEN(A1)
 
Last edited:
Upvote 0
Below is the code that was written by someone, it does find 12345 and then insert a row.

What i'm looking for is for macro to move one cell LEFT and then one cell UP (after 12345 is found)

Hopefully this helps.

Thanks.


Dim c As Range
Set rng = ActiveSheet.Range("b1:b5000")
For dblCounter = rng.Cells.Count To 1 Step -1
Set c = rng(dblCounter)
If c.Value Like "12345" Then
c.EntireRow.Insert
End If
Next dblCounter
 
Upvote 0
I don't think this should matter for your current question, but you should mention this fact wherever you ask a question on this (or any other) forum given that your version of Excel is nearly 20 years old and a lot has changed in Excel since then.




I cannot tell from your post what cell the 12345 is in, but are you sure that is all of the text in the cell (I am thinking you might have hidden spaces or non-breaking spaces in it)? Lets assume the 12345 is in cell A7 (correct the address if that guessed is wrong), put this formula in an empty cell and tell me what it returns...

=LEN(A1)

Here is the link to the picture of the data as to how it looks.

https://drive.google.com/open?id=1jOyR-FMN-Yyq2Q_VbVvGQa-7VFNgg1wY
 
Upvote 0
Maybe your version of Excel does matter (I'm thinking your version of Excel's VBA might not support the Find method that I used). Okay, since the code you post does work to insert the row, try modifying the code as I show in red below and see if that works for you...
Code:
Dim c As Range
Set rng = ActiveSheet.Range("b1:b5000")
For dblCounter = rng.Cells.Count To 1 Step -1
  Set c = rng(dblCounter)
  If c.Value Like "12345" Then
    c.EntireRow.Insert
    [B][COLOR="#FF0000"]c.Offset(-1,-1).Select[/COLOR][/B]
    [B][COLOR="#FF0000"]Exit For[/COLOR][/B]
  End If
Next dblCounter
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top