Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Autofilter and Delete(partial row deletion)

Status
Not open for further replies.

busy_bee

Programmer
Oct 9, 2020
14
US
Below is the code I posted earlier to filter and delete rows that meet certain criteria.
Code:
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?
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
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.
 
 https://files.engineering.com/getfile.aspx?folder=3e184aab-0f46-4289-aab2-588dd136d5ea&file=Sample_File.xlsx
First, apply autofilter only to proper area. Assuming that it is isolated range:

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

combo
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top