macro to insert rows at the start of a certain number

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
Platform
  1. Windows
I need a macro to insert 2 rows in Col A at the start of the first number in the column beginning with 153, 163, 173 & 193


See sample data of what the data should look like after 2 rows have been manually inserted t the start of the numbers beginning with 153, 163, 173 & 193



it would be appreciated if someone could assist me



Book1
A
1103951
2103966
3
4
5153001
6153009
7153080
8
9
10163001
11163007
12163954
13
14
15173006
16173080
17173950
18
19
20193200
21193205
22193450
Sheet1
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I have tried to write code to insert two rows at the start of first number beginning with 153,163,173,193 but the section below in highlighted in red



Code:
 myLastRow = myWorksheet.Cells.Find(What:= ”Whattofind", _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row




Code:
 Sub insertBlankRows_numbers()


Dim myFirstRow As Long

Dim myLastRow As Long

Dim myWorksheet As Worksheet
Dim Whattofind As Variant
Dim iCounter As Long

myFirstRow = 2

Set myWorksheet = Worksheets(“sheet1”)
Whattofind = Array("153*", "163*", "173*", "193*")


myLastRow = myWorksheet.Cells.Find(What:= ”Whattofind", _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

For iCounter = myLastRow To (myFirstRow + 1) Step -1

myWorksheet.Rows(iCounter).Insert Shift:=xlShiftDown

Next iCounter

End Sub





I have also posted on the link below


https://www.excelforum.com/excel-pr...en-specific-numbers-in-col-a.html#post4929707
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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