Run-time error "5" after range().delete runs before querytable.add

[subirias.s3]

#1 I'm not a programmer, just learning this stuff so please excuse the
"stupid" questions. Pleae explain the reason or fix in plain english
so I can understand and learn from this.

I'm pulling data from from the internet using the following WEBQUERY:
as a gosub routine. It works perfectly every single time! But when I
add either of the two following commands, I get the runtime error "5"
error.

pgname.Range("A1:R100").Delete (xlShiftUp)
pgname.Range("A1:R100").Delete

When I remove the code the WEBQUERY: works fine without a hitch.
Please help me i) understand why this is causing a problem and ii) how
can I get the specified range cleared before the WEBQUERY: routine
runs. By the way, the

...... broken code excerpt .....
WEBQUERY:
pgname.Range("A1:R100").Delete (xlShiftUp) '<-- executes!
With pgname.QueryTables.Add(Connection:= _ '<-- errors!
"URL;" & mwURL & scantype & "" _
, Destination:=rng)
.Name = scantype
...
..

 

[ward376]

Try clearing the range rather than deleting it.
Selection.Clear

 

[ward376]

Try clearing the range rather than deleting it.
Selection.Clear

 

[subirias.s3]

Try clearing the range rather than deleting it.
Selection.Clear

Ward, thanks for the suggestion it worked like a champ. Now can you
help me understand why the Selection.Delete caused the error but
Selection.Clear worked without a hitch?

Again thanks for helping me with this simple problem!

 

[ward376]

I'm not positive, because your adding the query table connection with
code, but query tables are associated with automatically created named
ranges and when they're deleted, the query is also.
It may also have to do with your variable rng but I don't know how your
setting it.

Ward376

 

[subirias.s3]

I'm not positive, because your adding the query table connection with
code, but query tables are associated with automatically created named
ranges and when they're deleted, the query is also.
It may also have to do with your variable rng but I don't know how your
setting it.

Ward376

Yeah it doesn't make too much sense to me especially if the code for
the qurey table connection remains COMPLETELY UNCHANGED and works
PERFECTLY until the .DELETE is introduced. So I guess the question is
why does .CLEAR works fine but not .DELETE?? Very odd in my opinion,
really don't think it has anything to do with the variables. But
here's the ranges spelled out. And the reason I'm using variables is
I'm pulling data from 15 different website tables and placing them into
8 different excel worksheets.

Again thanks for the help and education Ward!

.........................................
Set pgname = Sheet2
Set rng = pgname.Range("A1")

WEBQUERY:
pgname.Range("A1:R100").Clear
With pgname.QueryTables.Add(Connection:= _
"URL;" & mwURL & scantype & "" _
, Destination:=rng)
.........................................