Filter NOT EQUAL TO - dynamically to account for varied row counts

ChrisOK · #1 12-18-2017, 11:46 PM

Got stuck piecing together recordings when I got to the FILTERING need ---
Not sure how to alter it to be generic to use on different weekly reports from the PERSONAL library. Report will always look the same but number of rows (range) varies. Need the code to handle it dynamically (expand when needed) and not be hardcoded to only handle x number of rows.
=======================================
In other words:
1-Filter using Col B "NOT EQUAL to "IN"
2-Delete result rows (how ever many there be from wk to wk)
3-Turn off filter
=======================================
Here's what I recorded:

Code:

Sub Macro1_FilterNotEqual()
'
' Macro1_FilterNotEqual Macro
' AR not equal to IN in col B
'
    Range("B1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$G$35").AutoFilter Field:=2, Criteria1:="<>*IN*", _
        Operator:=xlAnd
    Rows("12:18").Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$G$31").AutoFilter Field:=2
    Range("A1").Select
End Sub

I'll get the same report every week w/ the same layout, but the total # of rows will vary from week to week.
Column B will be stagnate but, how do I edit these 3 areas to handle more generically:
ActiveSheet.Range("$A$1:$G$35").
Rows("12:18").Select
ActiveSheet.Range("$A$1:$G$31").

The above, was working with a Range of data A1:G35
Once I turned on Filters, it located some rows "NOT EQUAL TO "IN" "

Those unwanted rows with codes NOT EQUAL to "IN" were selected and deleted
Then the report was Unfiltered to put things back to only showing "IN" rows
This could vary from week to week depending on the total rows on the report

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word Count--counts nonbreaking spaces	zarkadasa	Word	1	01-05-2017 08:18 PM
Word 2011 error in character counts	Arbor Friend	Word	0	12-17-2016 12:56 PM
Data gathering from varied sources for different outcome letters	cs225	Word	2	10-19-2014 06:23 AM
Varied Print Edge Requirements (A4 and A3 in same document)	charlie1979	Word	1	01-27-2012 02:10 PM
Auto correct varied words in Word 2007	ertpresso	Word	0	04-11-2010 01:46 PM