Help: How do I create a column of fixed width data?

[limshady411]

I have a column that combines the data of every row and delimates by a |
..

Instead of deliminating by |, I want to make it fixed width depending
on the field. Here is what I have for the delimited by |. Any ideas on
how to code for the fixed width? For example, Column 1 a max of 1
character, Column 2 will be between 2-5, Column 3 will be 6-20, Column
4 will be 21-25, etc. If the data in the field is less than the total
fixed amount (IE: column 3 goes to 10 and there is 10 left to reach
20), I need the void to be blanks and for column 4 to begin at 21.
Here's what I have for the delimited code and it works. I just need the
fixed part going now. Thanks much everyone!

Code:
--------------------

Private Sub finalOutput(no As Long)
Dim c1 As String
Dim c2 As String
Dim c3 As String
Dim c4 As String
Dim c5 As String
Dim c6 As String
Dim c7 As String
Dim c8 As String
Dim c9 As String
Dim c10 As String
Dim c11 As String
Dim c12 As String
Dim c13 As String
Dim c14 As String
Dim c15 As String
Dim c16 As String
Dim c17 As String
Dim c18 As String
Dim c19 As String
Dim c20 As String
Dim c21 As String
Dim c22 As String
Dim c23 As String
Dim c24 As String
Dim colCount As Long
Dim rowCount As Long
Dim row As Long

Worksheets(3).Activate

colCount = countCs(no)
rowCount = countRs(no)

For row = 1 To rowCount

c1 = CStr(Worksheets(3).Cells(row, colCount - 23).Value)
c2 = CStr(Worksheets(3).Cells(row, colCount - 22).Value)
c3 = CStr(Worksheets(3).Cells(row, colCount - 21).Value)
c4 = CStr(Worksheets(3).Cells(row, colCount - 20).Value)
c5 = CStr(Worksheets(3).Cells(row, colCount - 19).Value)
c6 = CStr(Worksheets(3).Cells(row, colCount - 18).Value)
c7 = CStr(Worksheets(3).Cells(row, colCount - 17).Value)
c8 = CStr(Worksheets(3).Cells(row, colCount - 16).Value)
c9 = CStr(Worksheets(3).Cells(row, colCount - 15).Value)
c10 = CStr(Worksheets(3).Cells(row, colCount - 14).Value)
c11 = CStr(Worksheets(3).Cells(row, colCount - 13).Value)
c12 = CStr(Worksheets(3).Cells(row, colCount - 12).Value)
c13 = CStr(Worksheets(3).Cells(row, colCount - 11).Value)
c14 = CStr(Worksheets(3).Cells(row, colCount - 10).Value)
c15 = CStr(Worksheets(3).Cells(row, colCount - 9).Value)
c16 = CStr(Worksheets(3).Cells(row, colCount - 8).Value)
c17 = CStr(Worksheets(3).Cells(row, colCount - 7).Value)
c18 = CStr(Worksheets(3).Cells(row, colCount - 6).Value)
c19 = CStr(Worksheets(3).Cells(row, colCount - 5).Value)
c20 = CStr(Worksheets(3).Cells(row, colCount - 4).Value)
c21 = CStr(Worksheets(3).Cells(row, colCount - 3).Value)
c22 = CStr(Worksheets(3).Cells(row, colCount - 2).Value)
c23 = CStr(Worksheets(3).Cells(row, colCount - 1).Value)
c24 = CStr(Worksheets(3).Cells(row, colCount - 0).Value)

Worksheets(3).Cells(row, colCount + 1).Value = CStr(c1 + "| " + c2 + "| " + c3 + "| " + c4 + "| " + c5 + "| " + c6 + "| " + c7 + "| " + c8 + "| " + c9 + "| " + c10 + "| " + c11 + "| " + c12 + "| " + c13 + "| " + c14 + "| " + c15 + "| " + c16 + "| " + c17 + "| " + c18 + "| " + c19 + "| " + c20 + "| " + c21 + "| " + c22 + "| " + c23 + "| " + c24)

Next row

End Sub


Private Function countCs(no As Long) As Long
countCs = Worksheets(3).UsedRange.Columns.Count
End Function

Private Function countRs(no As Long) As Long
countRs = Worksheets(3).UsedRange.Rows.Count
End Function
--------------------


My output looks like:

letter | year | name | address

So with the fixed width, it should be:

A2005Limshady 123 N Blah
1234567890123456789012345678

I hope this makes sense. Thanks again!

 

[limshady411]

What I'm trying to do is basically the inverse of the Data --> Text to
Columns function. I hope this helps and you guys can help me. Thanks
again!