Can someone help me speed up my macro? I need to do a web query that
runs for potentially millions of numbers in order to search for a
needle in a haystack in an online database. Efficiency is crucial
since I have such a large range to search.
When I first run the code, it cycles through once every 3 seconds.
After 10,000 cycles it takes 10 seconds. Is there a way to keep the
macro from slowing down (maybe clearing out whatever is causing the
slowdown)?
Or can someone suggest a more efficient way to do this search?
Sub Macro1()
Dim n As Long
Dim m As Integer
Dim x As String
m = 1
For n = 209211900 To 209900000
x = "a" & m
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://sandgate.co.clark.nv.us/servlet/Assessor?
instance=pcl2&parcel=16" & n _
, Destination:=Range(x))
.Name = "Assessor?instance=pcl2&parcel=16" & n & "_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "5"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
If ActiveSheet.Range(x) = "" Then ActiveSheet.Range(x) = n
If ActiveSheet.Range(x) = n Then ActiveSheet.Range(x).ClearContents
If ActiveSheet.Range(x) <> "" Then ActiveSheet.Rows(m & ":" & m +
1).EntireRow.Delete
If ActiveSheet.Range(x) = "" Then m = m Else m = m + 1
Next n
End Sub
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
If you find any spam message in the group, please send an email to Ayush @
jainayus...@gmail.com
-~----------~----~----~----~------~----~------~--~---
