Delete a row on value

[michellin]

Hi there,

This morning i ask for macro that works great. But now i have a little issue to solve.

In a excel sheet in the column D, i got some value write in some cell. The value is ''ACTIVE''.

I try to make a macro, to everytime the macro find ''ACTIVE'' in D column, the macro delete that line and remove the blanck cell with push up data. (delete total line OR A to D), and continu to the next till the end(never the same amount of row).

Thanks a lot master of EXCEL

Michellin

 

[Micron]

My guess is your problem is that you are looping rows from top to bottom and removing rows. That will change the position of all the rows as the code runs. To avoid that, you loop over the rows from bottom to top.

Or, you've created a macro that does it only for one particular row and can't get it to work for other rows. You should state what results you want and the results you get, not just what you want to do. If you post code to help with diagnosis, please use code tags (vba button on posting toolbar).

 

[skillilea]

This may be what you are looking for:

Public Sub DeleteActiveRows()

    Dim nWs As Worksheet
    Dim rng As Range
    Dim i As Long
    
    Set nWs = ActiveSheet  ' change to your sheet ie.  ThisWorkbook.Sheets("WORKSHEET")
    Set rng = nWs.Range("A1").CurrentRegion
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    For i = rng.Rows.Count To 1 Step -1
        If LCase(rng(i, 4).Value) = "active" Then
            rng(i, 4).EntireRow.Delete
        End If
    Next

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Put this on your sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Me.Range("A1").CurrentRegion
    
    If Not Intersect(Target, rng) Is Nothing Then
        DeleteActiveRows
        Exit Sub
    End If
End Sub

 

[michellin]

This may be what you are looking for:

Public Sub DeleteActiveRows()

    Dim nWs As Worksheet
    Dim rng As Range
    Dim i As Long
   
    Set nWs = ActiveSheet  ' change to your sheet ie.  ThisWorkbook.Sheets("WORKSHEET")
    Set rng = nWs.Range("A1").CurrentRegion
   
    Application.EnableEvents = False
    Application.ScreenUpdating = False
   
    For i = rng.Rows.Count To 1 Step -1
        If LCase(rng(i, 4).Value) = "active" Then
            rng(i, 4).EntireRow.Delete
        End If
    Next

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Put this on your sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Me.Range("A1").CurrentRegion
   
    If Not Intersect(Target, rng) Is Nothing Then
        DeleteActiveRows
        Exit Sub
    End If
End Sub

Hi skillilea,

That's work great thanks a lot Your the king

Michellin