Delete Row based on cell contents
 

Hi. Is it possible to delete the entire row if the contents of cellA in
that row contains a "/"?

So, I'd like to scan an entire sheet, and delete all rows that have a "/"
somewhere within the cell contents in column A of each row. Thanks!

Delete Row based on cell contents
 

How well do you know vba? Look in vba help index for FINDNEXT

--
Don Guillett
SalesAid Software

"Steph" wrote in message
...
Hi. Is it possible to delete the entire row if the contents of cellA in
that row contains a "/"?

So, I'd like to scan an entire sheet, and delete all rows that have a "/"
somewhere within the cell contents in column A of each row. Thanks!

Delete Row based on cell contents
 

Hi Steph,

Try:
'==============
Public Sub DeleteRange()
Dim rng As Range
Dim rcell As Range
Dim delRng As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim LRow As Long
Dim CalcMode As Long
Const sStr As String = "/" '<<==== CHANGE

Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

LRow = SH.Cells(Rows.Count, "A").End(xlUp).Row

Set rng = SH.Range("A1").Resize(LRow) '<<== CHANGE

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rcell In rng.Cells
If InStr(1, rcell.Value, sStr, vbTextCompare) Then
If delRng Is Nothing Then
Set delRng = rcell
Else
Set delRng = Union(rcell, delRng)
End If
End If
Next rcell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
Else
'nothing found, do nothing
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<================

---
Regards,
Norman



"Steph" wrote in message
...
Hi. Is it possible to delete the entire row if the contents of cellA in
that row contains a "/"?

So, I'd like to scan an entire sheet, and delete all rows that have a "/"
somewhere within the cell contents in column A of each row. Thanks!

Delete Row based on cell contents
 

or this should look at fewer cells than for/next loop

Sub findsumifslash()
With Cells
On Error Resume Next
Set c = .Find("/", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Rows(c.Row).ClearContents
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With
Columns("A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
How well do you know vba? Look in vba help index for FINDNEXT

--
Don Guillett
SalesAid Software

"Steph" wrote in message
...
Hi. Is it possible to delete the entire row if the contents of cellA in
that row contains a "/"?

So, I'd like to scan an entire sheet, and delete all rows that have a
"/"
somewhere within the cell contents in column A of each row. Thanks!

Delete Row based on cell contents
 

Hi Don,

Columns("A").SpecialCells
(xlCellTypeBlanks).EntireRow.Delete

What if there were legitimate blank cells or the slash character were not
located in any cell?

---
Regards,
Norman



"Don Guillett" wrote in message
...
or this should look at fewer cells than for/next loop

Sub findsumifslash()
With Cells
On Error Resume Next
Set c = .Find("/", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Rows(c.Row).ClearContents
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With
Columns("A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
How well do you know vba? Look in vba help index for FINDNEXT

--
Don Guillett
SalesAid Software

"Steph" wrote in message
...
Hi. Is it possible to delete the entire row if the contents of cellA
in
that row contains a "/"?

So, I'd like to scan an entire sheet, and delete all rows that have a
"/"
somewhere within the cell contents in column A of each row. Thanks!

Delete Row based on cell contents
 

No problem is no / but the empty rows gotta go with this. Who needs em
anyway. <G

--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
Hi Don,

Columns("A").SpecialCells
(xlCellTypeBlanks).EntireRow.Delete

What if there were legitimate blank cells or the slash character were not
located in any cell?

---
Regards,
Norman



"Don Guillett" wrote in message
...
or this should look at fewer cells than for/next loop

Sub findsumifslash()
With Cells
On Error Resume Next
Set c = .Find("/", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Rows(c.Row).ClearContents
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With
Columns("A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
How well do you know vba? Look in vba help index for FINDNEXT

--
Don Guillett
SalesAid Software

"Steph" wrote in message
...
Hi. Is it possible to delete the entire row if the contents of cellA
in
that row contains a "/"?

So, I'd like to scan an entire sheet, and delete all rows that have a
"/"
somewhere within the cell contents in column A of each row. Thanks!

Delete Row based on cell contents
 

or

Sub findsumifslash_A()
With Cells
On Error Resume Next
Set c = .Find("/", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not c Is Nothing Then
firstaddress = c.Address
Do
mystr = mystr & "," & c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With
Range(Right(mystr, Len(mystr) - 1)).EntireRow.Delete
End Sub

--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
Hi Don,

Columns("A").SpecialCells
(xlCellTypeBlanks).EntireRow.Delete

What if there were legitimate blank cells or the slash character were not
located in any cell?

---
Regards,
Norman



"Don Guillett" wrote in message
...
or this should look at fewer cells than for/next loop

Sub findsumifslash()
With Cells
On Error Resume Next
Set c = .Find("/", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Rows(c.Row).ClearContents
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With
Columns("A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
How well do you know vba? Look in vba help index for FINDNEXT

--
Don Guillett
SalesAid Software

"Steph" wrote in message
...
Hi. Is it possible to delete the entire row if the contents of cellA
in
that row contains a "/"?

So, I'd like to scan an entire sheet, and delete all rows that have a
"/"
somewhere within the cell contents in column A of each row. Thanks!