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

ArviLaanemets · #2 12-19-2017, 12:44 AM

Define a dynamic name, which has range equal with filtered range;
Define Print Area for your report;
Set the Print Area equal to named range defined before.

No need for VBA here!

ChrisOK · #3 12-19-2017, 08:11 AM

Actually I DO need VBA to help process this step -- whether it be using the FILTER method or other ---maybe Dynamic was a bad word to use---sorry

I've got several things going on with VBA -- that's cleaning up a really cumbersome output from a main frame -- (the stage you are seeing it at within this attachment is after TONS of clean up and compilation have occurred to the report data using VBA) so this VBA step (IS VERY MUCH NEEDED) to actually clean out the unwanted line items in the weekly report that are not equal to "IN" invoice type...

Once this VBA step is complete another VBA step will follow it to perform other accounting functions... (there's probably 75+ steps to this that normally takes the person all day to clean up -- I'm trying to expedite the process for a person who knows nothing about vba and very basic formulas) -- since this is a repetitious weekly task -- it only makes sense to automate the clean up and output so it can be handed back over to their upper management. (there will never be a need to Print it -- it's only circulated as a soft copy once cleaned and compiled appropriately)

NoSparks · #4 12-19-2017, 09:11 AM

what you have can be made dynamic and reduced to three lines...

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
ActiveSheet.Columns("B").AutoFilter Field:=1, Criteria1:="<>*IN*", Operator:=xlAnd
    
    'Rows("12:18").Select
    'Selection.Delete Shift:=xlUp
ActiveSheet.UsedRange.Offset(1).Delete
    
    'ActiveSheet.Range("$A$1:$G$31").AutoFilter field:=2
ActiveSheet.Columns("B").AutoFilter
    
    'Range("A1").Select
    
End Sub

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