Delete Rows in which a given cell contains a text string

[melecotones8]

Hello,

I import text data into Excel, and have been able to build a macro to delimit it, delete blank rows, etc.

Now, what I need help with - and haven't found help I understand (i.e. code I can copy/paste):

I want the VB macro to go through column A. When it finds a cell that contains the string "Segment 1:" or "Segment 2:" (..."Segment ###"), I want the entire row to then be deleted and cells shifted up.

For example:

A B C
1 aih 555.001 3.6E-06
2 ajg blahblah blah blah
3 ppwq 7985.5552 2.001E-19
4 Potential/V i1/A i2/A
5
6 Segment #1:
7 0.501 5.556E-4 5.502E-4

I want a macro that sees that in Cell A6, the partial text match for SEGMENT occurs. Then, VB would delete row 6 and shift the cells up.

I don't need a real grandiose sub-routine, and "segment" only occurs when it is to be deleted - so I don't need a list of terms, just if the contents match "segment*".

Make sense? I have seen many postings for similar things, but I am relatively certain this is basic VB and you may be able to help. Thank you so much!

Cheers
:beerchug:

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim Running As Double

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value Like "*segment*" Then

.Rows(i).Delete
End If
Next i
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

[melecotones8]

Thank you very much for the code. I ran it preliminarily, and there aren't any errors, however the Macro was not successful in deleting 2/2 instances of rows with "segment" in column A. I will continue to play with it - but wanted to say thank you for the initial help and I will post a follow up soon.

[Bob Phillips]

small tweak



Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim Running As Double

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If LCase(.Cells(i, "A").Value) Like "*segment*" Then

.Rows(i).Delete
End If
Next i
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub