INSERT ROW WITH SPECIFIS TEXT IN A COLUMN

xenios

Board Regular
Joined
Sep 4, 2020
Messages
91
Office Version
  1. 2016
Platform
  1. Windows
Hi All!

How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F.

If I record the macro it gives me the following result.
VBA Code:
Sub INSERT()
'
' INSERT Macro
'

'
    Rows("2:2").Select
    Selection.INSERT Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "JUICE"
    Range("A2:G2").Select
    Range("G2").Activate
    With Selection
        .HorizontalAlignment = xlCenterAcrossSelection
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlLTR
        .MergeCells = False
    End With
    Selection.Copy
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 72
    Rows("82:82").Select
    Selection.INSERT Shift:=xlDown
    Range("A82").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "MELON"
    Range("A82:G82").Select
    Range("G82").Activate
    Selection.Copy
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 87
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 90
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 95
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 104
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 107
    ActiveWindow.ScrollRow = 108
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 114
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 123
    ActiveWindow.ScrollRow = 124
    Rows("132:132").Select
    Selection.INSERT Shift:=xlDown
    Range("A132").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "APPLE"
    Range("A132:G132").Select
    Range("G132").Activate
    Selection.Copy
    ActiveWindow.ScrollRow = 125
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 129
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 145
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 152
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 160
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 163
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 165
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 171
    Rows("183:183").Select
    Selection.INSERT Shift:=xlDown
    Range("A183").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "LIME"
    Range("B184").Select
End Sub
 

Attachments

  • insert.png
    insert.png
    176.5 KB · Views: 20

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Try using this Vba script:
VBA Code:
Sub Insert_Row()
'Modified  10/22/2020  8:25:02 AM  EDT
Dim st As String
st = InputBox("Enter Search String")
Dim ans As Long
Dim SearchString As String
Dim SearchRange As Range
SearchString = st
Dim lastrow As Long
lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Set SearchRange = Range("F2:F" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
ans = SearchRange.Row
Rows(ans).Insert
End Sub
 
Upvote 0
Try using this Vba script:
VBA Code:
Sub Insert_Row()
'Modified  10/22/2020  8:25:02 AM  EDT
Dim st As String
st = InputBox("Enter Search String")
Dim ans As Long
Dim SearchString As String
Dim SearchRange As Range
SearchString = st
Dim lastrow As Long
lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Set SearchRange = Range("F2:F" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
ans = SearchRange.Row
Rows(ans).Insert
End Sub

Thank you, I just need the wow to have same background color as row 1, and the text from column F to be written on the new row in CAPS with Center across selection.
 
Upvote 0
Try this:
Not sure what you mean by center across selection.
What selection?
VBA Code:
Sub Insert_Row()
'Modified  10/22/2020  9:06:47 AM  EDT
Dim st As String
st = InputBox("Enter Search String")
Dim ans As Long
Dim color As Long
color = Cells(1, "F").Interior.ColorIndex
MsgBox color
Dim SearchString As String
Dim SearchRange As Range
SearchString = st
Dim lastrow As Long
lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Set SearchRange = Range("F2:F" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox UCase(SearchString) & "  Not Found": Exit Sub
ans = SearchRange.Row
Rows(ans).Insert
Rows(ans).Interior.ColorIndex = color
Cells(ans, "F").Value = UCase(st)
End Sub
 
Upvote 0
Assuming you want the cell where we enter the text centered
Try this:

VBA Code:
Sub Insert_Row()
'Modified  10/22/2020  10:21:15 AM  EDT
Dim st As String
st = InputBox("Enter Search String")
Dim ans As Long
Dim color As Long
color = Cells(1, "F").Interior.ColorIndex
MsgBox color
Dim SearchString As String
Dim SearchRange As Range
SearchString = st
Dim lastrow As Long
lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Set SearchRange = Range("F2:F" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox UCase(SearchString) & "  Not Found": Exit Sub
ans = SearchRange.Row
Rows(ans).Insert
Rows(ans).Interior.ColorIndex = color
Cells(ans, "F").Value = UCase(st)
Cells(ans, "F").HorizontalAlignment = xlCenterAcrossSelection

End Sub
 
Upvote 0
across A to G as on the picture, and the text start from A tried to change it but didn't manage :(

For the moment it asks me to enter the word, can it somehow see it?

It also makes the grey color of the whole row till the end, can be until G?
 

Attachments

  • insert.png
    insert.png
    176.5 KB · Views: 13
Upvote 0
How do you expect it to see the word ? Where is the word?
Your original post said: with specific text in column F.

I do not know what specific Text.
What specific Text
 
Upvote 0
yes, the word/text is always in column F,
Let say it goes

Melon
Melon
Melon
Orange
Orange

So the row on top of of the melon it to show Melon
and before Orange to show Orange
if it's possible of course
 
Upvote 0
So this should happen no matter what the word. So if you have:
Alpha
Bravo
Charlie
Delta
Alpha
Dog
Cat

What would you want your end result to look like?
 
Upvote 0
Please find the pic attached. In my case the same word can't be located in different place, it is sorted by names.
 

Attachments

  • insert1.png
    insert1.png
    142 KB · Views: 12
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

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