Hi,
I would like to search a file for certain text and delete the entire row.
does anyone know code to do this?
Thanks
George

Hi George,
Instead of file you mean worksheet, but is the value in one column or anywhere on the worksheet.
Sub Delete_rows_based_on_ColA()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "standard" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End SubTo install a macro see http://www.mvps.org/dmcritchie/excel...avemacro---HTH, David McRitchie, Microsoft
MVP - Excel [site changed Nov. 2001]My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htmSearch Page:
http://www.mvps.org/dmcritchie/excel/search.htm "George" wrote in message
...
Hi,
I would like to search a file for certain text and delete the entire row.
does anyone know code to do this?
Thanks
George

Dave:
In a small test (here) stepping thru the code, I get
after:
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)

In immediate window I enter:
? Rng.address
$A$3,$A$5,$A$9:$A$11
Then after running line:
For i = rng.Count To 1 Step -1
I see by hovering over i that it has a value of 5 (O/K)
Then I step thru:
If LCase(rng(i).Value) = "ghj" _
Then rng(i).EntireRow.Delete

In immediate window I enter:
? Rng(5).address
and I get $A$7 << the 5th cell in full range A3:A11,
not $A$11

Can you assist (me)?
TIA,





"David McRitchie" wrote in message
...
Hi George,
Instead of file you mean worksheet, but is the value in one column or
anywhere on the worksheet.
Sub Delete_rows_based_on_ColA()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "standard" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End SubTo install a macro see
http://www.mvps.org/dmcritchie/excel...avemacro---HTH,
David McRitchie, Microsoft
MVP - Excel [site changed Nov. 2001]My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htmSearch Page:
http://www.mvps.org/dmcritchie/excel/search.htm "George"
wrote in message
...
Hi,
I would like to search a file for certain text and delete the entire
row.
does anyone know code to do this?
Thanks
George

Hi Jim, (and George as original poster)

The macro did exactly what I wanted it to do according to how I interpreted
George's question. SpecialCells as used limits the scope to cells that
have text values and by definition SpecialCells is limited to the UsedRange.

Try this macro it is a bit more interactive having an InputBox and will tell you
if you have no cells with text values in Column A.. I don't see anything like
less than or greater signs that might confuse some web based newsgroups
interfaces. i.e. from Google you often have to look at the original rather
than the HTML presentation.

Sub Delete_rows_based_on_ColA()
Dim cell As Range, rng As Range, i As Long
On Error Resume Next
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
If rng Is Nothing Then
MsgBox "No cells in Column A have text constants, " & _
"macro is terminating by lack of content"
Exit Sub
End If

Dim needle As String
needle = Application.InputBox("Supply cell content to " _
& "Column A that will get entire row deleted", _
"Dialog Box for row deletions", "standard")
If needle = "" Then
MsgBox "Cancelled by your command"
Exit Sub
End If
On Error GoTo 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = needle _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jim May" wrote in message news:%3eRf.240669$oG.237187@dukeread02...
Dave:
In a small test (here) stepping thru the code, I get
after:
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)

In immediate window I enter:
? Rng.address
$A$3,$A$5,$A$9:$A$11
Then after running line:
For i = rng.Count To 1 Step -1
I see by hovering over i that it has a value of 5 (O/K)
Then I step thru:
If LCase(rng(i).Value) = "ghj" _
Then rng(i).EntireRow.Delete

In immediate window I enter:
? Rng(5).address
and I get $A$7 << the 5th cell in full range A3:A11,
not $A$11

Can you assist (me)?
TIA,





"David McRitchie" wrote in message
...
Hi George,
Instead of file you mean worksheet, but is the value in one column or
anywhere on the worksheet.
Sub Delete_rows_based_on_ColA()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "standard" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End SubTo install a macro see
http://www.mvps.org/dmcritchie/excel...avemacro---HTH,
David McRitchie, Microsoft
MVP - Excel [site changed Nov. 2001]My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htmSearch Page:
http://www.mvps.org/dmcritchie/excel/search.htm "George"
wrote in message
...
Hi,
I would like to search a file for certain text and delete the entire
row.
does anyone know code to do this?
Thanks
George

David; Thank you for all this, but as a part of my
"total-leaning-experience" can you tell me why:
....................(near the bottom of my original note)
In immediate window I enter:
? Rng(5).address
and I get $A$7 << the 5th cell in full range A3:A11,
not $A$11 WHY?????

IS there an answer?
TIA,
Jim

"David McRitchie" wrote in message
...
Hi Jim, (and George as original poster)

The macro did exactly what I wanted it to do according to how I
interpreted
George's question. SpecialCells as used limits the scope to cells
that
have text values and by definition SpecialCells is limited to the
UsedRange.

Try this macro it is a bit more interactive having an InputBox and will
tell you
if you have no cells with text values in Column A.. I don't see
anything like
less than or greater signs that might confuse some web based newsgroups
interfaces. i.e. from Google you often have to look at the original
rather
than the HTML presentation.

Sub Delete_rows_based_on_ColA()
Dim cell As Range, rng As Range, i As Long
On Error Resume Next
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
If rng Is Nothing Then
MsgBox "No cells in Column A have text constants, " & _
"macro is terminating by lack of content"
Exit Sub
End If

Dim needle As String
needle = Application.InputBox("Supply cell content to " _
& "Column A that will get entire row deleted", _
"Dialog Box for row deletions", "standard")
If needle = "" Then
MsgBox "Cancelled by your command"
Exit Sub
End If
On Error GoTo 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = needle _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jim May" wrote in message
news:%3eRf.240669$oG.237187@dukeread02...
Dave:
In a small test (here) stepping thru the code, I get
after:
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)

In immediate window I enter:
? Rng.address
$A$3,$A$5,$A$9:$A$11
Then after running line:
For i = rng.Count To 1 Step -1
I see by hovering over i that it has a value of 5 (O/K)
Then I step thru:
If LCase(rng(i).Value) = "ghj" _
Then rng(i).EntireRow.Delete

In immediate window I enter:
? Rng(5).address
and I get $A$7 << the 5th cell in full range A3:A11,
not $A$11

Can you assist (me)?
TIA,





"David McRitchie" wrote in message
...
Hi George,
Instead of file you mean worksheet, but is the value in one column or
anywhere on the worksheet.
Sub Delete_rows_based_on_ColA()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "standard" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End SubTo install a macro see
http://www.mvps.org/dmcritchie/excel...avemacro---HTH,
David McRitchie, Microsoft
MVP - Excel [site changed Nov. 2001]My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htmSearch Page:
http://www.mvps.org/dmcritchie/excel/search.htm "George"
wrote in message
...
Hi,
I would like to search a file for certain text and delete the entire
row.
does anyone know code to do this?
Thanks
George

Hi Jim,
Sure there's an answer. With SpecialCells your range is no longer A3:A11
but is only particular cell ranges that are text cells, because we told
SpecialCells to select text cells only.

SpecialCells allows you process an entire column in a fraction of a
second if you don't have much data, as opposed to 3 minutes to process
an entire column in a loop even if you have only two rows of data. So the
idea is to limit the scope of the range using builtin features, if possible as
opposed to doing your own checking on each cell.. .

SpecialCells has one very egregious problem, if the range results in more
than 8,192 non-contiguous cells it uses the entire range and does not produce
an error (MSKB http://support.microsoft.com/?kbid=832293 )
Usually not a problem unless you have alternating rows of datatypes.
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jim May" wrote in message news:jdzRf.241175$oG.191238@dukeread02...
David; Thank you for all this, but as a part of my
"total-leaning-experience" can you tell me why:
...................(near the bottom of my original note)
In immediate window I enter:
? Rng(5).address
and I get $A$7 << the 5th cell in full range A3:A11,
not $A$11 WHY?????

IS there an answer?
TIA,
Jim

"David McRitchie" wrote in message
...
Hi Jim, (and George as original poster)

The macro did exactly what I wanted it to do according to how I
interpreted
George's question. SpecialCells as used limits the scope to cells
that
have text values and by definition SpecialCells is limited to the
UsedRange.

Try this macro it is a bit more interactive having an InputBox and will
tell you
if you have no cells with text values in Column A.. I don't see
anything like
less than or greater signs that might confuse some web based newsgroups
interfaces. i.e. from Google you often have to look at the original
rather
than the HTML presentation.

Sub Delete_rows_based_on_ColA()
Dim cell As Range, rng As Range, i As Long
On Error Resume Next
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
If rng Is Nothing Then
MsgBox "No cells in Column A have text constants, " & _
"macro is terminating by lack of content"
Exit Sub
End If

Dim needle As String
needle = Application.InputBox("Supply cell content to " _
& "Column A that will get entire row deleted", _
"Dialog Box for row deletions", "standard")
If needle = "" Then
MsgBox "Cancelled by your command"
Exit Sub
End If
On Error GoTo 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = needle _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jim May" wrote in message
news:%3eRf.240669$oG.237187@dukeread02...
Dave:
In a small test (here) stepping thru the code, I get
after:
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)

In immediate window I enter:
? Rng.address
$A$3,$A$5,$A$9:$A$11
Then after running line:
For i = rng.Count To 1 Step -1
I see by hovering over i that it has a value of 5 (O/K)
Then I step thru:
If LCase(rng(i).Value) = "ghj" _
Then rng(i).EntireRow.Delete

In immediate window I enter:
? Rng(5).address
and I get $A$7 << the 5th cell in full range A3:A11,
not $A$11

Can you assist (me)?
TIA,





"David McRitchie" wrote in message
...
Hi George,
Instead of file you mean worksheet, but is the value in one column or
anywhere on the worksheet.
Sub Delete_rows_based_on_ColA()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "standard" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End SubTo install a macro see
http://www.mvps.org/dmcritchie/excel...avemacro---HTH,
David McRitchie, Microsoft
MVP - Excel [site changed Nov. 2001]My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htmSearch Page:
http://www.mvps.org/dmcritchie/excel/search.htm "George"
wrote in message
...
Hi,
I would like to search a file for certain text and delete the entire
row.
does anyone know code to do this?
Thanks
George