4

I am trying to take data that was merged into one cell and retain the first 6 characters of that cell and move the remaining into the next column then proceed through the worksheet. I wrote the following macro and it runs successfully and then freezes excel to the point of a hard shut down. Any assistance would be greatly appreciated as I need to process thousands of rows of data

    Sub SplitCell()
         Dim ws As Worksheet
         Dim cell As Range
         Dim originalValue As String
         Dim firstSixChars As String
         Dim remainingChars As String

         ' Set the worksheet where your data is located 
           Set ws = Worksheets("Sheet1") 

         ' Loop through each cell in the specified column (e.g., Column A)
         For Each cell In ws.Range("A:A").Cells
             originalValue = cell.Value
             firstSixChars = Left(originalValue, 6)
             remainingChars = Mid(originalValue, 7) ' Extract characters after the first 6

         ' Write the extracted values to adjacent columns
           cell.Value = firstSixChars
           cell.Offset(0, 1).Value = remainingChars
         Next cell
     End Sub
Improve this question
2
  • 2
    Why are you looping the entire column? That is over a million iterations. Instead find the last row in the column and only loop to there.
    – Scott Craner
    Commented Apr 3 at 19:08
  • 2
    @ScottCraner that's not true. Unless we talk about a faulty export, using a range of A:A is perfectly normal. If I have a sheet with 27 rows, Range A:A will be 27 rows. But with a faulty export , you can have many empty rows below it, and then instead of just 27, you get 1000000. In that case, you have to just check if you are processing empty rows, and skip if there are like 5 consecutive rows.
    – LPChip
    Commented Apr 4 at 8:34

2 Answers 2

Reset to default
3

Maybe such a code?

Sub Split6()
    Dim ws As Worksheet
    ' Set the worksheet where your data is located 
    Set ws = Worksheets("Sheet1") 
    Dim src As Range
    Set src = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
    src.TextToColumns Destination:=ws.Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(6, 1))
End Sub
Improve this answer
1
  • 3
    -1 because you'r eeffectively using the range A:A. If you have many empty cells at the bottom, which often happens with a bad csv export, then this script won't make a difference. Instead, using range A:A but check during the loop if we're processing empty rows and count to 5 and abort after is the preferred method.
    – LPChip
    Commented Apr 4 at 8:36
1

Solution, assign specific range of cells rather than entire column...duh

Improve this answer
2
  • You'd created a very BIG loop. That'll do it. Well done figuring that out and solving it.
    – music2myear
    Commented Apr 3 at 19:37
  • 2
    This will work for your current sheet, but I bet the real problem is that you are working with a CSV export that has 1000000 empty rows at the bottom. If you remove those rows, your normal script will already work, or if you add code to detect the empty rows and break out of the loop, it will also work.
    – LPChip
    Commented Apr 4 at 8:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .