MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Problem with deleting a row

  Asked By: Misty    Date: Nov 08    Category: MS Office    Views: 528

Since I have received my fantastic book (Excel 2002 Power programming
with VBA), I did not bother you too often, did I?

But now, the book does not give me the answer.

I copied and adapted this code from Craig's reply to "Need help
w/creating macro to delete rows"

The sub I'm working with creates a Webquery that takes it's value
from cell A1 (in the moment because eventually, I would have more).

The first part of the code looks like this:
[The code that I am referring to comes in the second part.]

Dim Symbol As String
Dim ThisURL As String
Dim i, c As Double
(BTW, I would like to know what "Double" means: I've really searched
a lot before asking this)
Symbol = ActiveCell.Value &".C"

ThisURL = "URL;[the address of the web site] symb=" & Symbol
With ActiveSheet.QueryTables.Add(Connection:=ThisURL, Destination:= _
.Refresh BackgroundQuery:=False
End With

The query comes back with 2 rows, the first row with columns heading,
(the first heading being "Symbol", in cell "B1"), the second row with
the data that I need.

So the second part of the code is aimed at suppressing the first
(text) row:

c = Range("A10").End(xlUp).Row
For i = c To 1 Step -1
If Cells(i, 2) = "Symbol" Then
Rows(i).Delete Shift:=xlUp
End If

This works perfect, except that it erases at the same time the symbol
that I wrote in Cell "A1"
Now I have tried to change the destination of the web query to Range

The data from the webquery come exactly where I want them, the
text "Symbol" is now in cell "B2".

But the second part of the code that suppresses the text row does not
work anymore.
Its like if I had taken it away form the sub, nothing happens.
To me it's a mystery.

Any idea?



No Answers Found. Be the First, To Post Answer.

Didn't find what you were looking for? Find more on Problem with deleting a row Or get search suggestion and latest updates.