Dave Peterson wrote:

Option Explicit
Sub Convert2Text2()

Dim iCol As Long
With ActiveSheet
'assumes that you'll skip 6 columns between data|text to columns
For iCol = .Range("ah1").Column To .Range("Fa1").Column Step 6
With .Range(.Cells(1, iCol), .Cells(255, iCol))
If Application.CountA(.Cells) 0 Then
.TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
End If
End With
Next iCol
End With
End Sub

Nice! Thank you, it looks like it has successfully converted all these
columns to Text datatype (Preview show data for these columns), I
changed one thing, I used Step 1 instead of Step 6 for I don't need to
skip any column.

btw, minor separate problem, during data import to sql server 2000, DTS
complained about one particular column, "Data source column X is too
large for the specified buffer size, which I'll check with sql server
ng to see where/how to extend buffer size.