Excel VBA - Custom Filter using Named Range -

Excel VBA - Custom Filter using Named Range -

i have column of info on worksheet, contained within big workbook working on, named vendor , want write macro in vba allows me filter particular vendor, vendor a.

so far have this:

sub filtervendor() ' ' filtervendor macro ' ' activesheet.range("$a:$bb").autofilter field:=21, criteria1:= _ "vendor a" end sub

the problem have location of vendor column may alter - other columns added and/or deleted before it, causing shift accordingly - , want know if possible reference particular column/block of info save macro becoming useless.

ideally, reference range (vendor phone call named range) that, no matter happens construction of worksheet, filter work correctly when macro run.

how can done?

further info:

i suspect field: criteria causing problem appears absolutely define column filtered; though have specified vendor column named vendor named range, , though named range reference moves along said vendor column, column filtered lies @ position specified in field:. how can fixed in order work how want to?

i have tried:

...field:=range("vendor")

and

...field:=(range("vendor"))

but both of these cause error.

interesting challenge! tackled by:

using named range, "vendor", describe (i think) writing little helper function, doesvendorexist, ensures named range "vendor" exists writing little helper function, vendorcolnum, returns column number of "vendor" applying .autofilter based on number returned vendorcolnum

here screenshot of (admittedly small) workbook tested on:

here helper functions , slightly-modified filtervendor2 subroutine:

option explicit sub filtervendor2() dim datasheet worksheet dim vendorcol long 'set references up-front set datasheet = thisworkbook.worksheets("data") '... 'doing other stuff '... 'here create sure named range called vendor exists if not doesvendorexist(thisworkbook) msgbox ("dangit! there not named range vendor in workbook! exiting...") exit sub end if 'define vendor column vendorcol = vendorcolnum(datasheet) 'here create sure vendor named range not empty if vendorcol = 0 msgbox ("bummer! named range vendor nil on datasheet, exiting...") exit sub end if 'otherwise, let's filtering! datasheet.range("$a:$bb").autofilter field:=vendorcol, criteria1:="snookerfan" '... 'doing other cool stuff , finishing '... end sub 'input : pass function worksheet on vendor range defined 'output : column number vendor range exists in 'special: if vendor range nothing, homecoming 0 public function vendorcolnum(sheet worksheet) long 'if vendor range missing, homecoming 0 , exit if sheet.range("vendor") nil vendorcolnum = 0 exit function end if 'otherwise, homecoming real column number vendorcolnum = sheet.range("vendor").column end function 'input : pass function workbook we'd check vendor 'output : true/false... vendor exist? 'special: none public function doesvendorexist(book workbook) boolean dim nm name 'assume false, test each name , flip flag if vendor found 'assume false, test each name , flip flag if vendor found doesvendorexist = false each nm in book.names if ucase(nm.name) = ucase("vendor") '<~ error here, did not paste on .name doesvendorexist = true exit function end if next nm end function

finally, resulting filtered range. should find adding or removing columns on info sheet not wreck code.

edit: apologies error on checking named range sloppy copy/paste worked through issue myself. have updated code above , highlighted error (was checking nm see if matched "vendor", should have been checking nm.name see if matched ucase("vendor").

excel vba