At a glance it looks like you need to remove the dot from in front of
"Cells", but I haven't tested it.

set rCTyLst=wsCtyLst.Range(Cells(2, sCtyLstCol), Cells(lRow, sCtyLstCol))


"davegb" wrote:

I've tried to research this on the net, but couldn't find anything that
directly deals with my confusion. That is, what does With/End With do
different than using the object name and the appropriate code?
Walkenbach says it will save running time. Elsewhere I read that it
saves entry time. But it must do more than that. For example, some code
I just wrote, when I used the spreadsheet name, wouldn't run. When I
used With/End with, it worked.

set rCTyLst=wsCtyLst.Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))

This gives an "invalid or unqualified reference error"

With wsCtyLst
Set rCtyLst = .Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))
End With

This code runs. Very confusing. Why?
Thanks in advance.