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

Status
Not open for further replies.

busy_bee

Programmer
Oct 9, 2020
14
US
I would like to Autofilter an Excel worksheet based on criteria and delete the visible rows. I know Excel VBA has such capability.

Link

Can you help me in writing such code in VBScript?
 
Hi,

Turn on your macro recorder and record the process of
1) SELECTing all the ROWs of data
2) FILTERing the ROWs you want DELETEd
3) DELETE
4) unFILTER

Turn off your recorder and Alt+F11 to activate the VB Editor. COPY the recorded code and PASTE here for help customizing.

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 figured out a solution. Here is the attached code.

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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top