Newbie question defining ranges

E

ElPresidente

For i = 1 To 17
Set uRange = Sheet1.Range(Cells(1, i), Cells(19, i))
uRange.AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Sheet3.Cells(1, i), Unique:=True
Next i

Can someone explain why this code works if I write it as above, but if
I change the CopyToRange to Sheet3.Range(Cells(1,i)) I just get
errors?
 
D

Dave Peterson

You're lucky that it works. And if Sheet1 isn't the activesheet, then it won't
work.

set uRange = sheet1.range(sheet1.cells(1,i), sheet1.cells(19,i))

or to save typing:
with sheet1
set urange = .range(.cells(1,i),.cells(19,i))
end with

or even:
set urange = sheet1.cells(1,i).resize(19,1)
(it's 19 rows by 1 column)

===========

If you did this
sheet3.range(sheet3.cells(1,i))

it's like typing:
sheet3.range(sheet3.cells(1,i).value)
(since you ony provided a single argument to cells().)

And I'm guessing that sheet3.cells(1,i) did not contain anything that looked
like an address. So it blows up.
 
Top