Need help with Row deleting macro

[shukero]

Hi everyone,

I'm fairly new to macros as a whole. (Hence I've been coming here a lot for some help) And I've been trying to figure out a macro on my own that would delete an entire row of data in if the text "Ad" is populated into any cell in column B.

Here is the code I have so far:

Sub Delete_Ads_Rows()
Dim rng As Range
Dim cel As Range

  With Worksheets("NSB")
    r = .Cells(.Rows.count, 2).End(xlUp).Row
    Set rng = .Range(.Cells(2, 2), .Cells(r, 2))

        For Each cel In rng.Cells
            If cel.Value = "Ad" Then
            .Cells(cel.Row, 2).Entire.Row.Delete
        End If
    Next cel
  End With
End Sub

I have a feeling it has something to do with the ".Entire.Row.Delete" but I'm stumped can someone help me out?

thanks,
~Mike

 

[hiker95]

shukero,

We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[JoeMo]

You didn't say what your problem is, but one problem I see is that when you use a For each construct, VBA begins at the topmost row in the specified range and proceeds down the rows. This is a problem when you are deleting rows because when you delete a row all the rows beneath it shift up causing you to skip rows. Say the first row to be deleted is row 3 and that row 4 should be deleted too. When row 3 is deleted, what was row 4 is now row 3, but your code has already dealt with row 3 so what was row 4 is missed.

Here's an alternative that begins the process at the bottom-most row and proceeds up.

Sub Delete_Ads_Rows()
Dim rng As Range
Dim i As Long
  With Worksheets("NSB")
    R = .Cells(.Rows.Count, 2).End(xlUp).Row
    Set rng = .Range(.Cells(2, 2), .Cells(R, 2))
    For i = rng.Rows.Count To 1 Step -1
        If rng(i) = "Ad" Then
            Rows(rng(i).Row).Delete
        End If
    Next i
  End With
End Sub

 

[hiker95]

shukero,

If I understand you correctly, then here is a macro solution for you to consider that does not do any looping in the rows in column B.

Sample raw data:

Excel 2007
	A	B	C
1		Ads
2		Not this
3		Ad
4		Ad
5		not this
6		Ad
7		Ad
8		not this
9		Ad
10		not this
11
NSB

And, after the macro:

Excel 2007
	A	B	C
1		Ads
2		Not this
3		not this
4		not this
5		not this
6
7
8
9
10
11
NSB

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub shukero()
' hiker95, 12/12/2016, ME980421
Application.ScreenUpdating = False
With Sheets("NSB")
  With .Columns("B")
    .Replace "Ad", "#N/A", xlWhole, , False
    On Error Resume Next
    .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete xlUp
    On Error GoTo 0
  End With
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the shukero macro.

 

[MARK858]

Just another option...

Sub Filtdelete()
    Application.ScreenUpdating = False
    
    With Worksheets("NSB").Range("B1:B" & Worksheets("NSB").Range("B" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:="Ad"
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
        .AutoFilter
    End With
    
    Application.ScreenUpdating = True
End Sub