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

In spite of all your help, I'm still confused as to why
if ? Rng.address (tested in the immediate window)
= $A$1,$A$2,$A$6:$A$8
which to me are cells A1, A2, A6, A7, A8
WHY DOES ? Rng(5)
Produce A5

And Not A8.
That's all I want to know.

Sorry for my dumbness, but..
(I have to live with it.)

"David McRitchie" wrote in message
...
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

Alan Beban has some notes on this form of addressing at Chip Pearson's site:
http://www.cpearson.com/excel/cells.htm

Simplistically, this kind of addressing just uses the top left cell of the first
area in that range.

You've got to be very careful with discontiguous ranges--they don't always
behave the way you would think they would:

Dim rng As Range
Set rng = Range("a1,c9,e5,g5")
MsgBox rng.Rows.Count
MsgBox rng.EntireRow.Count

What do you expect to be returned for each msgbox? What did you actually get?

What did this give you?

MsgBox Intersect(Columns(1), rng.EntireRow).Cells.Count

Discontiguous ranges...they're sneaky!


Jim May wrote:

In spite of all your help, I'm still confused as to why
if ? Rng.address (tested in the immediate window)
= $A$1,$A$2,$A$6:$A$8
which to me are cells A1, A2, A6, A7, A8
WHY DOES ? Rng(5)
Produce A5

And Not A8.
That's all I want to know.

Sorry for my dumbness, but..
(I have to live with it.)

"David McRitchie" wrote in message
...
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












--

Dave Peterson

Thanks Dave.
I went to CP's Site and have printed off the article by Alan, to read and
study.
Much appreciated.
Jim May


"Dave Peterson" wrote:

Alan Beban has some notes on this form of addressing at Chip Pearson's site:
http://www.cpearson.com/excel/cells.htm

Simplistically, this kind of addressing just uses the top left cell of the first
area in that range.

You've got to be very careful with discontiguous ranges--they don't always
behave the way you would think they would:

Dim rng As Range
Set rng = Range("a1,c9,e5,g5")
MsgBox rng.Rows.Count
MsgBox rng.EntireRow.Count

What do you expect to be returned for each msgbox? What did you actually get?

What did this give you?

MsgBox Intersect(Columns(1), rng.EntireRow).Cells.Count

Discontiguous ranges...they're sneaky!


Jim May wrote:

In spite of all your help, I'm still confused as to why
if ? Rng.address (tested in the immediate window)
= $A$1,$A$2,$A$6:$A$8
which to me are cells A1, A2, A6, A7, A8
WHY DOES ? Rng(5)
Produce A5

And Not A8.
That's all I want to know.

Sorry for my dumbness, but..
(I have to live with it.)

"David McRitchie" wrote in message
...
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












--

Dave Peterson