Text to Columns on all Columns Starting w/"E6"

T

Terri

Hello,
Could you please tell me "how to" (with code) do Text to
Columns (Fixed Width, General) on all columns starting
with E6, to bottom of the used range?
I never know how many columns will be on the spreadsheet.
I just know that everything starts on row 6 in all
columns and begins with column E.
I would really appreciate your help.
Thank You,
Terri
 
G

Guest

Oops...Did I post this to the wrong site? Should I
repost this to the programming section?
 
D

Dave Peterson

I think there is some confusion with your post.

All columns starting with E6 is confusing to me at least.

Did you meant you wanted all the cells from E6:E(lastrow)?

Or all the columns E6:E9999, F6:F9999, G6:G9999, and so forth?

Here's some code that may get you started. You should record a macro when you
do the text to columns manually to get that layout (fixed width and General) the
way you want.

Option Explicit
Sub myTextToColumns()

Dim myRng As Range
Dim totFormulas As Long

With Worksheets("sheet1")
Set myRng = .Range("e6", .Cells(.Rows.Count, "E").End(xlUp))
If Application.CountA(myRng) = 0 Then
'do nothing, no data
Else
totFormulas = 0
On Error Resume Next
totFormulas = myRng.Cells _
.SpecialCells(xlCellTypeFormulas).Cells.Count
On Error GoTo 0
If totFormulas > 0 Then
MsgBox "Please no formulas!"
Else
Application.DisplayAlerts = False
myRng.TextToColumns Destination:=myRng.Cells(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
Array(4, 1), Array(10, 1), Array(18, 1))
Application.DisplayAlerts = True
End If
End If
End With
End Sub

The application.displayalerts = false suppresses the "do you want to overwrite
the values in the adjacent columns. If you have data next to column E, you'll
want to move that data to the right in your code. Just record a macro when you
do it manually and plop it in the code right before the
"application.displayalerts = false" line.
 
D

Dave Peterson

Ooh. I should have mentioned that I assumed that you wanted E6:E9999 only.
 
G

Guest

Sorry for any confusion. Yes, I meant all the populated
cells in all columns from E6:E(last row), F6:F(last row),
G6:G(last row), until the columns are blank.
Thank You again!
Terri
 
D

Dave Peterson

If you know that your data is fixed width, then you know how many fields are in
the column that need to be splitting up.

I used 5 fields (and inserted 4 columns for each column) in this example:

Be careful. You're limited to 256 columns. You can't insert a lot of columns
without blowing up real good.


Option Explicit
Sub myTextToColumns2()

Dim myRng As Range
Dim totFormulas As Long
Dim cCtr As Long
Dim NumberOfFields As Long
Dim LastCol As Long

NumberOfFields = 5 'total number of fields to split

With Worksheets("sheet1")

totFormulas = 0
On Error Resume Next
totFormulas = .Range("e6", .Cells.SpecialCells(xlCellTypeLastCell)) _
.SpecialCells(xlCellTypeFormulas).Cells.Count
On Error GoTo 0

If totFormulas > 0 Then
MsgBox "Please no formulas!"
Exit Sub
End If

For cCtr = .Cells(6, .Columns.Count).End(xlToLeft).Column To 5 Step -1

Set myRng = .Range(.Cells(6, cCtr), _
.Cells(.Rows.Count, cCtr).End(xlUp))

If Application.CountA(myRng) = 0 Then
'do nothing, no data
Else
.Columns(cCtr + 1).Resize(, NumberOfFields - 1).Insert
Application.DisplayAlerts = False
myRng.TextToColumns Destination:=myRng.Cells(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
Array(4, 1), Array(10, 1), Array(18, 1))
Application.DisplayAlerts = True
End If
Next cCtr
End With
End Sub
 
T

Terri

Thank you soooooo much for all your help!
-----Original Message-----
If you know that your data is fixed width, then you know how many fields are in
the column that need to be splitting up.

I used 5 fields (and inserted 4 columns for each column) in this example:

Be careful. You're limited to 256 columns. You can't insert a lot of columns
without blowing up real good.


Option Explicit
Sub myTextToColumns2()

Dim myRng As Range
Dim totFormulas As Long
Dim cCtr As Long
Dim NumberOfFields As Long
Dim LastCol As Long

NumberOfFields = 5 'total number of fields to split

With Worksheets("sheet1")

totFormulas = 0
On Error Resume Next
totFormulas = .Range("e6", .Cells.SpecialCells (xlCellTypeLastCell)) _
.SpecialCells (xlCellTypeFormulas).Cells.Count
On Error GoTo 0

If totFormulas > 0 Then
MsgBox "Please no formulas!"
Exit Sub
End If

For cCtr = .Cells(6, .Columns.Count).End (xlToLeft).Column To 5 Step -1

Set myRng = .Range(.Cells(6, cCtr), _
.Cells(.Rows.Count, cCtr).End(xlUp))

If Application.CountA(myRng) = 0 Then
'do nothing, no data
Else
.Columns(cCtr + 1).Resize(, NumberOfFields - 1).Insert
Application.DisplayAlerts = False
myRng.TextToColumns Destination:=myRng.Cells(1), _
DataType:=xlFixedWidth,
Click to expand...
FieldInfo:=Array(Array(0, 1), _
 
Top