Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why is range empty? | Excel Discussion (Misc queries) | |||
empty variable range | Excel Discussion (Misc queries) | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Validation List with Variable Lengths & Invisible Empty Cells | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |