Merge the contents of two columns using VBA

[pjkaphlen]

Hi. I have this vba code. This imports .txt file and runs a pivot table based on the range selected by the user. But the main problem now is after the pivot table. Kindly see my code below:

Sub InputText()'
' InputText Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Dim Ret


Ret = Application.GetOpenFilename("txt files (*.txt), *.txt")


If Ret <> False Then
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Ret, Destination:=Range("$B$1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If


Dim r As Range
On Error Resume Next
Set r = Application.InputBox("Select a range", Type:=8)
On Error GoTo 0
If r Is Nothing Then Exit Sub


Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
r, Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(3, 1).Select
Sheets("Sheet1").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Internal Part Number")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Site Id")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("IPN Description")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ABC Category")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Commodity Code")
.Orientation = xlRowField
.Position = 5
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Commodity Description" _
)
.Orientation = xlRowField
.Position = 6
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Commodity Family")
.Orientation = xlRowField
.Position = 7
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Supplier Name")
.Orientation = xlRowField
.Position = 8
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date Received")
.Orientation = xlRowField
.Position = 9
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Unit price")
.Orientation = xlRowField
.Position = 10
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Currency Code")
.Orientation = xlRowField
.Position = 11
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Unit of Measure Recieved")
.Orientation = xlRowField
.Position = 12
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Freight Terms")
.Orientation = xlRowField
.Position = 13
End With
Sheets("Sheet1").Name = "Pivot Table"
Range("A4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Internal Part Number"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
Range("B4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Site Id").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
Range("C4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("IPN Description"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
Range("D4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("ABC Category").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
Range("E4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Commodity Code").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
Range("F4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Commodity Description"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
Range("G4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Commodity Family"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
Range("H4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date Received").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
Range("I4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Supplier Name").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
Range("J4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Unit price").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
Range("K4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Currency Code"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
Range("L4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Unit of Measure Recieved"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
Range("M4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Freight Terms").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
Range("A:M").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Pivot Data"
Range("C:C").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
Application.CutCopyMode = False
Sheets("Pivot Table").Select
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date Received").AutoSort _
xlDescending, "Date Received"
Selection.Copy
Sheets("Pivot Data").Select
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("K:K").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RC[3]"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A51"), Type:=xlFillDefault
Range("A2:A51").Select
ActiveWindow.SmallScroll Down:=-45
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&RC[1]"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B51"), Type:=xlFillDefault
Range("B2:B51").Select

The end part (bold portion) copies the content of column D to Column A.
Then, the contents of Column A is merged to the contents of Column C and is placed in Column B.

I am thinking of simply using D:D&C:C so that I won't have to copy at column A anymore. Now, here's the prob.

simply want merge the data AS LONG AS the data of a cell in column C doesn't contain "(blank)" (at the end of the raw data of column C is "(blank)".


Can anyone help me figure out how to do this?
I can simply merge the contents of columns D and C but that will increase the used range of the excel to row 65,000 which will make the file size larger.