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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 11:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com