Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a field and delete row
Hi Guys,
I would like to delete the row if a column "status" changes to "closed. this is what I got, but of course it is not working can you help? Dim cnclrge As Range ' If ActiveCell.Offset(0, 17) = "Closed" Then ' ' cnclrge.EntireRow.Delete ' End If Thanks Albert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a field and delete row
If ActiveCell.Offset(0, 17) = "Closed" Then activecell.EntireRow.Delete
-- Don Guillett Microsoft MVP Excel SalesAid Software "Albert" wrote in message ... Hi Guys, I would like to delete the row if a column "status" changes to "closed. this is what I got, but of course it is not working can you help? Dim cnclrge As Range ' If ActiveCell.Offset(0, 17) = "Closed" Then ' ' cnclrge.EntireRow.Delete ' End If Thanks Albert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a field and delete row
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "Q:Q" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value = "Closed" Then Target.EntireRow.Delete End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Albert" wrote in message ... Hi Guys, I would like to delete the row if a column "status" changes to "closed. this is what I got, but of course it is not working can you help? Dim cnclrge As Range ' If ActiveCell.Offset(0, 17) = "Closed" Then ' ' cnclrge.EntireRow.Delete ' End If Thanks Albert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a field and delete row
hi
your not setting the active cell to equal cnclrge. Dim cnclrge As Range set cnclrge = activecell If ActiveCell.Offset(0, 17) = "Closed" Then cnclrge.EntireRow.Delete 'or Activecell.EntireEow.Delete end if Personally i dislike using Activecell and perfer using variables but everyone has your own way. Regards FSt1 "Albert" wrote: Hi Guys, I would like to delete the row if a column "status" changes to "closed. this is what I got, but of course it is not working can you help? Dim cnclrge As Range ' If ActiveCell.Offset(0, 17) = "Closed" Then ' ' cnclrge.EntireRow.Delete ' end if Thanks Albert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a field and delete row
Thanks guys, sorted.
"FSt1" wrote: hi your not setting the active cell to equal cnclrge. Dim cnclrge As Range set cnclrge = activecell If ActiveCell.Offset(0, 17) = "Closed" Then cnclrge.EntireRow.Delete 'or Activecell.EntireEow.Delete end if Personally i dislike using Activecell and perfer using variables but everyone has your own way. Regards FSt1 "Albert" wrote: Hi Guys, I would like to delete the row if a column "status" changes to "closed. this is what I got, but of course it is not working can you help? Dim cnclrge As Range ' If ActiveCell.Offset(0, 17) = "Closed" Then ' ' cnclrge.EntireRow.Delete ' end if Thanks Albert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete calculated field | Excel Discussion (Misc queries) | |||
Unable to change field settings in calculated field in a pivot tab | Excel Discussion (Misc queries) | |||
How do I use a keyboard to change a PivotTable or delete a field? | Excel Discussion (Misc queries) | |||
How do I use a keyboard to change a PivotTable or delete a field? | Excel Discussion (Misc queries) | |||
If field in column is blank, delete if.... | Excel Programming |