vba - Convert a COUNTIF range recorded in an Excel macro to a variable range -
i'm looking converting countif
range recorded in excel macro variable range. using excel vba manipulate monthly file has variable amount of records each month.
i recorded macro modify , filter records , able convert of ranges variable ranges of site. however, struggling converting range variable range countif
function shown below identifies duplicated id
s in column color. column filtered color.
range("a2").select range(selection, selection.end(xldown)).select selection.formatconditions.add type:=xlexpression, formula1:= _ "=countif($a$2:$a$7977,a2)>=2" selection.formatconditions(selection.formatconditions.count).setfirstpriority selection.formatconditions(1).interior .patterncolorindex = xlautomatic .color = 65535 .tintandshade = 0 end selection.formatconditions(1).stopiftrue = true range(“a1”).select selection.autofilter activesheet.range(“$a$1:$p$7977”).autofilter field:=1, criteria1:=rgb(255,255,0),operator:=xlfilter cell color
you need function find last row of column. see below:
function getlastrow(sht worksheet, col string) integer getlastrow = sht.range(col & cstr(sht.rows.count)).end(xlup).row end function
call function in every place need put end of range, name of sheet, , letter of column parameters. see below how might work:
"=countif($a$2:$a$" & getlastrow(sheets("sheet1"),"a") & ",a2)>=2"
Comments
Post a Comment