If Range Variable isn't Empty
 

Excel Experts,

In my code, I want to delete certain rows if they have certain characters in
a cell in a certain column.

My spreadsheet and code are similar to the following:

A B
1 Symbol Activity Type
2 AGEN Buy
3 IBM Div
4 DELL Sell
5 HWP Qmi
6 AGEN Buy

The code is supposed to work by changing any entries of "Div" or "Qmi" to a
blank, and then deletes the rows with a blank. I tried to set it up to do
nothing if there aren't any Div or Qmi entries, but the code does nothing
even when there ARE Div or Qmi entries. I don't believe the IsEmpty function
is the one I need or I am using it wrong.

Pls. advise how to indicate that if the range variable Divs does reference
cells, to do something.


Cells.Find(What:="Activity Type").Select
Range(Selection, Selection.End(xlDown)).Select

Application.DisplayAlerts = False
On Error Resume Next

Set ActivityType = Selection

ActivityType.Replace What:="Div", Replacement:=""

ActivityType.Replace What:="Qmi", Replacement:=""

Set Divs = ActivityType.SpecialCells(xlCellTypeBlanks)

If IsEmpty(Divs) = False Then

Divs.EntireRow.Delete

End If

Thank you,
Alan


--
achidsey

If Range Variable isn't Empty
 

seems you are trying to define Divs as a range of non-contiguous cells

I would think that you need to step through activitytype

dim myCell as range

For each myCell in ActivityType
if myCell.value = "" then mycell.entireRow.ClearContents
next myCell

this will leave blank lines - If you want rows removed then you need to
start at the bottom of the ActivityType range and step - 1 so that row
deletions do not crash the row counting



"achidsey" wrote:

Excel Experts,

In my code, I want to delete certain rows if they have certain characters in
a cell in a certain column.

My spreadsheet and code are similar to the following:

A B
1 Symbol Activity Type
2 AGEN Buy
3 IBM Div
4 DELL Sell
5 HWP Qmi
6 AGEN Buy

The code is supposed to work by changing any entries of "Div" or "Qmi" to a
blank, and then deletes the rows with a blank. I tried to set it up to do
nothing if there aren't any Div or Qmi entries, but the code does nothing
even when there ARE Div or Qmi entries. I don't believe the IsEmpty function
is the one I need or I am using it wrong.

Pls. advise how to indicate that if the range variable Divs does reference
cells, to do something.


Cells.Find(What:="Activity Type").Select
Range(Selection, Selection.End(xlDown)).Select

Application.DisplayAlerts = False
On Error Resume Next

Set ActivityType = Selection

ActivityType.Replace What:="Div", Replacement:=""

ActivityType.Replace What:="Qmi", Replacement:=""

Set Divs = ActivityType.SpecialCells(xlCellTypeBlanks)

If IsEmpty(Divs) = False Then

Divs.EntireRow.Delete

End If

Thank you,
Alan


--
achidsey

If Range Variable isn't Empty
 

Change the IsEmpty line to

If Not Divs Is Nothing Then


--

HTH

RP
(remove nothere from the email address if mailing direct)


"achidsey" (notmorespam) wrote in message
...
Excel Experts,

In my code, I want to delete certain rows if they have certain characters
in
a cell in a certain column.

My spreadsheet and code are similar to the following:

A B
1 Symbol Activity Type
2 AGEN Buy
3 IBM Div
4 DELL Sell
5 HWP Qmi
6 AGEN Buy

The code is supposed to work by changing any entries of "Div" or "Qmi" to
a
blank, and then deletes the rows with a blank. I tried to set it up to do
nothing if there aren't any Div or Qmi entries, but the code does nothing
even when there ARE Div or Qmi entries. I don't believe the IsEmpty
function
is the one I need or I am using it wrong.

Pls. advise how to indicate that if the range variable Divs does reference
cells, to do something.


Cells.Find(What:="Activity Type").Select
Range(Selection, Selection.End(xlDown)).Select

Application.DisplayAlerts = False
On Error Resume Next

Set ActivityType = Selection

ActivityType.Replace What:="Div", Replacement:=""

ActivityType.Replace What:="Qmi", Replacement:=""

Set Divs = ActivityType.SpecialCells(xlCellTypeBlanks)

If IsEmpty(Divs) = False Then

Divs.EntireRow.Delete

End If

Thank you,
Alan


--
achidsey

If Range Variable isn't Empty
 

Thank you for the responses. A great help. Alan

--
achidsey


"Bob Phillips" wrote:

Change the IsEmpty line to

If Not Divs Is Nothing Then


--

HTH

RP
(remove nothere from the email address if mailing direct)


"achidsey" (notmorespam) wrote in message
...
Excel Experts,

In my code, I want to delete certain rows if they have certain characters
in
a cell in a certain column.

My spreadsheet and code are similar to the following:

A B
1 Symbol Activity Type
2 AGEN Buy
3 IBM Div
4 DELL Sell
5 HWP Qmi
6 AGEN Buy

The code is supposed to work by changing any entries of "Div" or "Qmi" to
a
blank, and then deletes the rows with a blank. I tried to set it up to do
nothing if there aren't any Div or Qmi entries, but the code does nothing
even when there ARE Div or Qmi entries. I don't believe the IsEmpty
function
is the one I need or I am using it wrong.

Pls. advise how to indicate that if the range variable Divs does reference
cells, to do something.


Cells.Find(What:="Activity Type").Select
Range(Selection, Selection.End(xlDown)).Select

Application.DisplayAlerts = False
On Error Resume Next

Set ActivityType = Selection

ActivityType.Replace What:="Div", Replacement:=""

ActivityType.Replace What:="Qmi", Replacement:=""

Set Divs = ActivityType.SpecialCells(xlCellTypeBlanks)

If IsEmpty(Divs) = False Then

Divs.EntireRow.Delete

End If

Thank you,
Alan


--
achidsey