Delete a row on value

michellin

Board Regular
Joined
Oct 4, 2011
Messages
57
Office Version
  1. 2019
Platform
  1. Windows
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
 

Attachments

  • CLEAN UP ACTIVE.JPG
    CLEAN UP ACTIVE.JPG
    137.9 KB · Views: 6

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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).
 
Upvote 0
This may be what you are looking for:

VBA Code:
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


VBA Code:
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
 
Upvote 0
Solution
This may be what you are looking for:

VBA Code:
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


VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top