2

I found and modified the below code to remove all rows that don't contain "NHO_Global" in column F, but it deletes everything (it does leave the header row, though).

What did I do wrong and how can I fix it? I need it to leave just the rows that contain "NHO_Global" in column F (simply filtering won't work for my purposes - they need to be deleted).

Sub KeepOnlyNHOGlobal()

    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ActiveWorkbook.Sheets("NHO_Global")

    lastRow = ws.Range("F" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("F1:F" & lastRow)

    'Filter and delete all but the header row
    With rng
        .AutoFilter Field:=1, Criteria1:="<>NHO_GLOBAL"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    'Turn off the filters
    ws.AutoFilterMode = False

End Sub

UPDATE: it turns out that having filters turned on (even with none applied) was the problem. Turning filters off allowed the code to run as expected.

  • The only mistake in the code is AutoFilter Field:=1, it should be AutoFilter Field:=6, since your criteria is in column F – Rajesh S Dec 8 '18 at 8:04
1

I have good news. The problem is in your data, not in your code. Before:

Before:

enter image description here

and after:

enter image description here

Note:

Make sure your column F values are not corrupted by extraneous characters!

  • How was you able to tell that from the information given by the OP ? this is not a dispute btw just curious to learn – PeterH Dec 7 '18 at 14:56
  • I've triple checked and repasted the data multiple times now and it keeps happening. I don't understand what the issue could be. – Lenny Dec 7 '18 at 15:00
  • @PeterH I took the code that was posted and ran it myself, un-changed, The posted code worked perfectly! – Gary's Student Dec 7 '18 at 15:08
  • 1
    @Gary'sStudent it looks like I had to remove filters! No filters were actually applied, but filters were turned on. Running the code with filters turned on (but none applied) deleted everything. Running the code with filters turned off made it run perfectly. I had no idea that having filters turned on would affect anything at all! – Lenny Dec 7 '18 at 15:21
  • @Lenny GOOD JOB! Update your original question with this information and I will up-vote it. – Gary's Student Dec 7 '18 at 15:23

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.