VBA code to continue formatting using text to column when some cells are blank

Question

As my VBA knowledge isn't extensive, I usually Record the macros I need. As I have columns with both Date and Time in the same cell, I used Excel's 'Text to Columns' feature and recorded it, coming up with the code below. However once there is a blank cell in any row, all the cells below that do not get formatted!

In my searching, I've found that the solution is based looping the code to perform the action cell by cell but I haven't found how to do what's needed using that, plus looks extremely complex!

I'm asking if there's a way to ignore the blank cells and continue with the formatting.

This is the Text to Column code but is there code I can add before or after to ignore the blank cells and still keep the code below?

Columns("S:S").Select
Selection.Insert Shift:=xlToRight
Range("R2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("R2"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 2), Array(9, 1)), TrailingMinusNumbers:=True
Columns("S:S").Select
Selection.Delete Shift:=xlToLeft

Columns("T:T").Select
Selection.Insert Shift:=xlToRight
Range("S2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("S2"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 2), Array(9, 1)), TrailingMinusNumbers:=True
Columns("T:T").Select
Selection.Delete Shift:=xlToLeft

Columns("U:U").Select
Selection.Insert Shift:=xlToRight
Range("T2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("T2"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 2), Array(9, 1)), TrailingMinusNumbers:=True
Columns("U:U").Select
Selection.Delete Shift:=xlToLeft

score 0 · Answer 1 · edited May 23 '17 at 10:28

Change,

Range(Selection, Selection.End(xlDown)).Select

... to,

Range(Cells(2, "T", Cells(Rows.Count, "T").End(xlUp)).Select

Change the "T" to the appropriate column.

I humbly suggest you make the move to get away from using the Range .Select and Range .Activate methods to accomplish your tasks. Recorded code is a good way to start but you should be prepared to modify the code to remove Select and Selection if you plan to keep the code for future use. See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

For example:

With Worksheets("Sheet1")
    .Columns("S:S").Insert Shift:=xlToRight
    With .Range(.Cells(2, "R"), .Cells(Rows.Count, "R").End(xlUp))
        .TextToColumns Destination:=.Range("R2"), DataType:=xlFixedWidth, _
                       FieldInfo:=Array(Array(0, 2), Array(9, 1)), _
                       TrailingMinusNumbers:=True
    End With
    .Columns("S:S").Delete Shift:=xlToLeft

    .Columns("T:T").Insert Shift:=xlToRight
    With .Range(.Cells(2, "S"), .Cells(Rows.Count, "S").End(xlUp))
        .TextToColumns Destination:=.Range("S2"), DataType:=xlFixedWidth, _
                       FieldInfo:=Array(Array(0, 2), Array(9, 1)), _
                       TrailingMinusNumbers:=True
    End With
    .Columns("T:T").Delete Shift:=xlToLeft

    .Columns("U:U").Insert Shift:=xlToRight
    With .Range(.Cells(2, "T"), .Cells(Rows.Count, "T").End(xlUp))
        .TextToColumns Destination:=.Range("T2"), DataType:=xlFixedWidth, _
                       FieldInfo:=Array(Array(0, 2), Array(9, 1)), _
                       TrailingMinusNumbers:=True
    End With
    .Columns("U:U").Delete Shift:=xlToLeft
End With

Using a loop to run through column R, S and T (columns 18, 19 and 20) will clean up your code further.

Dim c As Long
With Worksheets("Sheet1")
    For c = 18 To 20
        With .Range(.Cells(2, c), .Cells(.Rows.Count, c).End(xlUp))
            .TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, _
                           FieldInfo:=Array(Array(0, xlMDYFormat), Array(10, xlSkipColumn))
        End With
    Next c
End With

I've applied a xlMDYFormat to the date. If your dates are DMY use xlDMYFormat or check for other available formats with xlColumnDataType Enumeration.

It is unclear on why you are inserting a column, processing the [.TextToColumns](https://msdn.microsoft.com/en-us/library/office/ff193593.aspx) and subsequently deleting the column. You can just skip the second column with **xlSkipColumn** (see [xlColumnDataType Enumeration](https://msdn.microsoft.com/en-us/library/bb241018.aspx)). — , Jul 15 '16 at 06:52
Thank you so very much! To answer your question, I'm just removing the columns I inserted to the right which I created purely to put the times in after using the Text to Column command, but I don't need the times so I delete those columns! — D. Goh, Jul 18 '16 at 07:31
Hi again! Wanted to let you know that I managed to find an easy way to do it without looping and deleting columns! Unfortunately I'm using Select! Columns("R:T").Select Selection.NumberFormat = "m/d/yyyy" — D. Goh, Jul 19 '16 at 07:39
Hi again! Wanted to let you know I managed to find an easy way to do it without looping or deleting columns! Unfortunately I'm using Select! `Columns("R:T").Select` `Selection.NumberFormat = "m/d/yyyy"` — D. Goh, Jul 19 '16 at 07:52
Nice but not the same thing as stripping off the time to a date. Be careful you don't mismatch a lookup. — , Jul 19 '16 at 08:26

VBA code to continue formatting using text to column when some cells are blank

1 Answers1