VBA Clear contents issue with Excel Mac 2016

[Mikeymike15]

Hi all,

I'm frustrated....I made one change to my code and now i'm getting whacky results

The line below that is bold, underlined, italicized is the element that I added, and now my clear contents line is going from C7:T27 to C7:AW160

The step thru function doesn't seem to help me. Mac Excel VBA always seems to be a little screwy.

Any advice?

Thanks,
Mike

Sub GetData()


Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer
Dim j As Integer
Dim k As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Set DataSheet = ActiveSheet


' Range("C7:T27").ClearContents
Range("C7:T27").CurrentRegion.ClearContents
i = 7
qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> ""
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + Range("C2")
Range("c1") = qurl
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With


j = Range("C7").End(xlDown).Row


For k = 7 To j


Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Common Stoc", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Common St", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Co St", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Co", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. (The)", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Com", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc.", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Incorporated C", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", I", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Series 1", "")


Next


Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False


'turn calculation back on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
' Range("C7:H2000").Select
' Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("C:C").ColumnWidth = 25
Rows("7:2000").RowHeight = 16
Columns("J:J").ColumnWidth = 8.5
Range("h2").Select


End Sub

 

[Mikeymike15]

Sub GetData()


Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer
Dim j As Integer
Dim k As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Set DataSheet = ActiveSheet


' Range("C7:T27").ClearContents
Range("C7:T27").CurrentRegion.ClearContents
i = 7
qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> ""
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + Range("C2")
Range("c1") = qurl
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With


j = Range("C7").End(xlDown).Row


For k = 7 To j


Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Common Stoc", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Common St", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Co St", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Co", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. (The)", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Com", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc.", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Incorporated C", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", I", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Series 1", "")


Next


Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False


'turn calculation back on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
' Range("C7:H2000").Select
' Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("C:C").ColumnWidth = 25
Rows("7:2000").RowHeight = 16
Columns("J:J").ColumnWidth = 8.5
Range("h2").Select


End Sub

 

[Mikeymike15]

disregard, I figured it out.....My CurrentRegion was formatted incorrectly....

Mods you can delete this thread....

thanks,
Mike