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

#### sous2817

##### Well-known Member
Hello everyone,

Currently I'm using:

Code:
``````    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!

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Andrew Poulsom

##### MrExcel MVP
Why are you resizing to 1 column?

#### sous2817

##### Well-known Member
probably because I recycled some code and didn't notice. but even when I take it out:

Code:
``.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

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

#### sous2817

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

Replies
20
Views
1K
Replies
1
Views
452
Replies
1
Views
2K
Replies
17
Views
526
Replies
5
Views
388

Threads
1,191,183
Messages
5,985,172
Members
439,944
Latest member
Vangelis74

### 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

### 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