excel - How to use set ranges -

i looking way improve on better way write piece of code that'll if there clients "first list" in "second list" , copy data on sheet named "found".

it goes this:

dim row long, row2 long, found long dim id string, prtgtid string, gtid2 string application.screenupdating = false prtgtid = "b" gtid2 = "d" row = 2 row2 = 2 found = 0 while row <= cells(rows.count, prtgtid).end(xlup).row     id = cells(row, prtgtid)     sheets("second list").select     while row2 <= cells(rows.count, gtid2).end(xlup).row         if (id = cells(row2, gtid2))             rows(row2).select             selection.copy             sheets("found").select             rows(2).select             selection.insert shift:=xldown             sheets("first list").select             rows(row).select             selection.copy             sheets("found").select             rows(2).select             selection.insert shift:=xldown             sheets("second list").select             found = found + 1         end if         row2 = row2 + 1     loop     sheets("first list").select     row = row + 1     row2 = 2 loop sheets("blank").select cells(2, 3) = found application.screenupdating = true 

this simplified version of actual code (which larger , containing lot of exits loops organizing alphabetically), right i'm concerned using ".select" function what's consuming of processing time.

i know i've seen there's way using

set rng = range(cells(2, prtgtid),cells(cells(rows.count, gtid2).end(xlup).row, prtgtid)) each cell in rng     code next cell 

or along lines, can't seem able find detailed tutorial goes further "it's faster way".

considering need format in rows kept when copying them on "found", there way change make go faster?

activate , select emulate user keystrokes , if set application.screenupdating false, don't need select objects. should avoid these methods (see here interesting article why , when select:http://dailydoseofexcel.com/archives/2004/04/27/beginning-vba-select-and-activate/). declare variables (set rng...) or deal directly objects instead.

sub test() dim row long, row2 long, found long dim id string, prtgtid string, gtid2 string application.screenupdating = false prtgtid = "b" gtid2 = "d" row = 2 row2 = 2 found = 0 while row <= sheets("first list").cells(rows.count, prtgtid).end(xlup).row     id = sheets("first list").cells(row, prtgtid)    ' sheets("second list").select    sheets("second list")     while row2 <= .cells(rows.count, gtid2).end(xlup).row         if (id = .cells(row2, gtid2))             .rows(row2).copy             sheets("found").rows(2).insert shift:=xldown             sheets("first list").rows(row).copy             sheets("found").rows(2).insert shift:=xldown             found = found + 1         end if         row2 = row2 + 1     loop     end '    sheets("first list").select     row = row + 1     row2 = 2 loop sheets("blank").cells(2, 3) = found application.screenupdating = true  end sub 

you see few lines less , no more select. see how works "with" statement (used here example) can useful. (i assume launch macro sheet "first list" activated, that's why add sheets("first list")) programming way avoid kind of error (so can launch macro without worrying wich sheet activate)