Autofilter and Delete(partial row deletion)

[busy_bee]

Below is the code I posted earlier to filter and delete rows that meet certain criteria.

const xlUp = -4162
lastRow = ws1.UsedRange.Rows.Count
lastColumn = ws1.UsedRange.Columns.Count

Set rngDataBlock = ws1.Range(ws1.Cells(1,1),ws1.Cells(lastRow,lastColumn))

rngDataBlock.AutoFilter 4,"a",,,False

If ws1.Cells(1,4).Value = "a" Then
	ws1.AutoFilter.Range.Delete xlUp
Else
	ws1.AutoFilter.Range.Offset(1).Delete xlUp
End If

ws1.AutoFilterMode = False

I would now only like to delete and shift cells up the filtered rows only from Column D to F. Can I get help with code for this task?

 

[SkipVought]

Hi,

It just makes no sense in a table to shift some of the columns of data from one row to another, leaving other rows without data that has been shifted elsewhere.

Nonsense!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[busy_bee]

I have attached a sample Excel file. The purpose of my task is to perform a delete and shift cells up on the following ranges. "D4:F4","D6:F6","D7:F7". While doing so, I would like to keep the data in Columns H to J intact.

 

[combo]

First, apply autofilter only to proper area. Assuming that it is isolated range:

[tt]Set rngDataBlock = ws1.Cells(4,3).CurrentRegion[/tt]

combo