use .autofilter & delete to remove a section of data and not the whole row issue

[sous2817]

Hello everyone,

Currently I'm using:

    lr = .Range("C" & Rows.Count).End(xlUp).Row
    If lr = 1 Then lr = 2
    Set rng = .Range("A1:D" & lr)
        .Range("B2:B" & lr).Formula = "=IF(A2="""",VLOOKUP(C2,$G$2:$H$" & lr2 & ",2,0),VLOOKUP(A2,$G$2:$H$" & lr2 & ",2,0))"
        .Range("B2:B" & lr).Calculate
        .Range("B2:B" & lr).Value = .Range("B2:B" & lr).Value
    With rng
        .AutoFilter Field:=2, Criteria1:="#N/A"
        'assume the first row had headers
        On Error Resume Next
        .Offset(1, 0).Resize(.Rows.Count - 1, 1). _
            SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
        On Error GoTo 0
        
        'remove the autofilter
        .AutoFilter
    End With

to delete all of the resulting "#N/A"s from my formula. It works fine except it deletes the entire row and not just the area between .Range("A1:D" & lr). Am I missing something, or can someone help me tweak the code so that it only deletes the within the area and not the whole row.

Thanks in advance!

 

[Andrew Poulsom]

Why are you resizing to 1 column?

 

[sous2817]

probably because I recycled some code and didn't notice. but even when I take it out:

.Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp

the whole row is still deleted. I just need A:D deleted. I could do it w/ a loop, but I'm trying to avoid that.

 

[Andrew Poulsom]

You can only delete entire rows in an AutoFilter range. Try it manually and you will see - Excel will prompt 'Delete entire row?'.

 

[sous2817]

ah...well there you go. Thanks for the tip! I'll keep that in mind moving forward.