If macro, when keyword phrase is in certain cell

rcb007

Board Regular
Joined
Nov 12, 2020
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Trying to figure out how to create a macro that would find a keyword phrase in a cell value. If that keyword phrase exists; select the cell value below to the end of the list and (Cut & Paste) it over the keyword phrase cell value.

I current do this manually, For instance with the below list, the keyword phrase would be in cell A28 = Allow Supercritical Depth = ,#TRUE#.

Then I would select the cell value below A29 to the end of the list (List is usually very long). Then Cut all those values be overwriting the A28 Value. Which looks like the second list.


Book1
A
1SI Units? ,#FALSE#
2Total No. Lines = ,87
3Starting HGL = ,922.205
4Return Period Index = ,10
5Min Cover = ,3
6Zero Min Cover at outfalls = ,#TRUE#
7Design Vel = ,2.5
8Design Alignment = ,1
9SI Units? ,#FALSE#
10Total No. Lines = ,87
11Starting HGL = ,922.205
12Return Period Index = ,10
13Min Cover = ,3
14Zero Min Cover at outfalls = ,#TRUE#
15Design Vel = ,2.5
16Min Slope = ,0.2
17Max Slope = ,10
18Min Pipe Size = ,12
19Max Pipe Size = ,96
20Default Pipe n-value = ,0.012
21Design Alignment = ,1
22Allow smaller downstream pipe sizes = ,#FALSE#
23N-Value of Inlets = ,0.016
24Grate Design Depth = ,0.3
25Accumulate Known Qs = ,#TRUE#
26Use Inlet Captured Flows in System = ,#FALSE#
27Auto Compute Junct. Loss Coeff. = ,#TRUE#
28Allow Supercritical Depth = ,#TRUE#
29Supress Pipe Travel Time = ,#FALSE#
30Minimum Tc used to calc Intensity,5
31Check for Inlet Control? = ,#FALSE#
32Correct for EGL discrepancy = ,#FALSE#
33Using HDS-5 Method? = ,#FALSE#
34Curb Opening = ,Horiz
35List continues.....
36
37
Sheet1



Book1
A
1SI Units? ,#FALSE#
2Total No. Lines = ,87
3Starting HGL = ,922.205
4Return Period Index = ,10
5Min Cover = ,3
6Zero Min Cover at outfalls = ,#TRUE#
7Design Vel = ,2.5
8Design Alignment = ,1
9SI Units? ,#FALSE#
10Total No. Lines = ,87
11Starting HGL = ,922.205
12Return Period Index = ,10
13Min Cover = ,3
14Zero Min Cover at outfalls = ,#TRUE#
15Design Vel = ,2.5
16Min Slope = ,0.2
17Max Slope = ,10
18Min Pipe Size = ,12
19Max Pipe Size = ,96
20Default Pipe n-value = ,0.012
21Design Alignment = ,1
22Allow smaller downstream pipe sizes = ,#FALSE#
23N-Value of Inlets = ,0.016
24Grate Design Depth = ,0.3
25Accumulate Known Qs = ,#TRUE#
26Use Inlet Captured Flows in System = ,#FALSE#
27Auto Compute Junct. Loss Coeff. = ,#TRUE#
28Supress Pipe Travel Time = ,#FALSE#
29Minimum Tc used to calc Intensity,5
30Check for Inlet Control? = ,#FALSE#
31Correct for EGL discrepancy = ,#FALSE#
32Using HDS-5 Method? = ,#FALSE#
33Curb Opening = ,Horiz
34List continues.....
Sheet1



I hope I made sense. If the keyword phrase does not exist, it would do nothing.

Thank you again.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,543
Office Version
  1. 2010
Platform
  1. Windows
According to your attachment a VBA demonstration as a beginner starter :​
VBA Code:
Sub Demo1()
    Dim V
        V = Application.Match("Allow Supercritical Depth = ,#TRUE#", [A1].CurrentRegion, 0)
        If IsNumeric(V) Then Rows(V).Delete
End Sub
 

rcb007

Board Regular
Joined
Nov 12, 2020
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Thank you so much, works great!
 

Forum statistics

Threads
1,143,638
Messages
5,719,977
Members
422,253
Latest member
frankie2016tata

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
Top