David McRitchie said: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/getstarted.htm#havemacro---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"
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
Jim May said: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 said: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/getstarted.htm#havemacro---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"
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 McRitchie said: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 said: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 said: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/getstarted.htm#havemacro---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"
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
Jim May said: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:
IS there an answer?
TIA,
Jim
David McRitchie said: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 said: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,
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/getstarted.htm#havemacro---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"
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 McRitchie said: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 said: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 said: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
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,
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/getstarted.htm#havemacro---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"
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
Jim said: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 said: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 said: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
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
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,
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/getstarted.htm#havemacro---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"
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 said: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 said: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 said: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
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
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
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,
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/getstarted.htm#havemacro---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"
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