Text to column macro

[peteywarner]

Hi, I was hoping someone may be able to help me here.

I am trying to add to a macro script, a part that will convert text to columns. Currently I run a small macro that sorts the data and deletes what I don't want, and then it uses formulae in excel cells to manipulate the data into columns. I wanted if possible to get the macro to do all of it, as excel falls over once I reach about 25,000 lines; due to the number of formulae involved (excel 2000). From the one text field I extract fourteen different columns, obviously each formulae is different mostly due to Left, Mid and Right combinations; but there are other varying IF statements also.

Thanks in advance.

Pete

[Bob Phillips]

Do it in Excel with the macro recorder on. You will have your code.

[peteywarner]

Thanks for the quick response; but sorry I'm not sure I understand, do what with the recorder on? run my existing macro? type in the formula?

[Bob Phillips]

To do the text to columns bit, then add that code to your existing.

[peteywarner]

Hi, sorry I probably didn't explain myself well enough, I don't actually use the text to column function as my knowledge of VBA is very basic, and I couldn't work out how to join certain columns on only some of the rows back up again based on certain criteria within the macro, so I have done it with "IF" statements in excel cells, I was wondering if I could just add these "IF" statements into the macro.

Thanks

Pete

[Bob Phillips]

This is a bit like fishing in the dark, but I guess it would be something like

Public Sub ProcessData()
Const TEST_COLUMN As String = "A"    '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Const delim As String = ", "    'change to suit
With ActiveSheet
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
   For i = 2 To iLastRow
      .Cells(i, "F").Value = .Cells(i, "A") & delim & _
      .Cells(i, "B") & delim & _
      .Cells(i, "C") & delim & _
      .Cells(i, "D") & delim & _
      .Cells(i, "E")
   Next I
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End With
End Sub

[peteywarner]

Cool thanks for that I will have a play around with it, thanks for all your help.