0

I would like to delimit my time text to the separate columns:

Enter image description here

I used the following approach:

VBA code for "Text to Column - Fixed Width" - loop

But I don't know how to apply it for the column range down to the last row.

My code looks like this:

Dim wks As Worksheet
Dim lRow As Long

Set wks = ThisWorkbook.ActiveSheet

lRow = wks.Range("D" & wks.Rows.Count).End(xlUp).Row

With wks
   .Cells(4, lRow).TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
      Semicolon:=True, Space:=False
   .Range("S1").CurrentRegion.Columns.AutoFit
 End With

And I am getting an error:

No data was selected to parse.

How can I make it work with my range of cells?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Geographos
  • 827
  • 2
  • 23
  • 57

1 Answers1

0

When performing text-to-column in VBA, you do not need to set the last row for the range. Just set it until end of the row will do. Please perform a test with the following by replacing cells with column only:

With wks
   .Columns("D:D").TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
      Semicolon:=True, Space:=False
   .Range("S1").CurrentRegion.Columns.AutoFit
 End With

Or:

wks.range("D4:D").TextToColumns ....
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Kin Siang
  • 2,644
  • 2
  • 4
  • 8
  • Still almost the same... It seems to work, but my Excel is completely frozen. I just had a few records delimited randomly across the range and I couldn't even shut down my Excel afterwards, It does work if I have space, but it can't definitely go with the semicolon. – Geographos Jun 23 '21 at 11:33
  • Normally setting to end of the row won't affect the speed performance, do you make a improper looping and cause `infinitely loop`? You will only need set to `last row` when you are perform looping checking, however `text-to-column` does not apply looping – Kin Siang Jun 23 '21 at 12:08