INSERT ROW WITH SPECIFIS TEXT IN A COLUMN

xenios

Board Regular
Joined
Sep 4, 2020
Messages
86
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: 13

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,023
Office Version
  1. 2013
Platform
  1. Windows
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
 

xenios

Board Regular
Joined
Sep 4, 2020
Messages
86
Office Version
  1. 2016
Platform
  1. Windows
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.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,023
Office Version
  1. 2013
Platform
  1. Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,023
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
 

xenios

Board Regular
Joined
Sep 4, 2020
Messages
86
Office Version
  1. 2016
Platform
  1. Windows
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: 8

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,023
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
 

xenios

Board Regular
Joined
Sep 4, 2020
Messages
86
Office Version
  1. 2016
Platform
  1. Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,023
Office Version
  1. 2013
Platform
  1. Windows
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?
 

xenios

Board Regular
Joined
Sep 4, 2020
Messages
86
Office Version
  1. 2016
Platform
  1. Windows
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: 8

Watch MrExcel Video

Forum statistics

Threads
1,114,457
Messages
5,548,033
Members
410,823
Latest member
ymjaskiel
Top