VBA Macro to Process Google Keywords CSV file into a usable, clickable Excel file (CODE)

Sub process_google_keywords_csv_download()

Dim lastrow As Integer
Dim c As Range
‘find last row
Range(“a65000”).End(xlUp).Select
lastrow = ActiveCell.Row

‘insert last row
ActiveSheet.ListObjects.Add(xlSrcRange, Range(“$A$1:$D$” & lastrow), , xlYes).Name = _
“Table1”
Range(“Table1[#All]”).Select
ActiveSheet.ListObjects(“Table1”).TableStyle = “TableStyleLight9”

Range(“Table1[Top URLS]”).Select
Selection.Replace What:=”[“, Replacement:=””, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=”]”, Replacement:=””, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Range(“E1”).Select
ActiveCell.FormulaR1C1 = “WithSpace”
Range(“E2”).Select
ActiveCell.FormulaR1C1 = “=SUBSTITUTE([@[Top URLs]],””:http://””,”” http://””)”

‘select table column
Range(“Table1[WithSpace]”).Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns(“E:E”).Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range(“Table1[[#Headers],[WithSpace]]”), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)) _
, TrailingMinusNumbers:=True

Range(“Table1[Top URLS]”).Delete
Range(“Table1[#All]”).ColumnWidth = 25
Range(“Table1[WithSpace]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column1]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column2]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column3]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column4]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column5]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column6]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column7]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“Table1[Column8]”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *