Type Mismatch Error on Empty Cells?

Question

With the following code displayed below, I seem to be getting Type Mismatch errors as soon as it hits empty cells. Usually, the max range is A1:MZ1. The code splits a cell with 4 lines of text into 4 rows.

Dim SplitText
Dim myRange As Range, k As Range

Set myRange = Sheet2.Range("A1:LN1")

For Each k In myRange
    SplitText = Split(k, vbLf)
    k.Resize(UBound(SplitText) + 1) = Application.Transpose(SplitText)
Next

Initially, I thought this might have been due to a transpose length limit, but that doesn't appear to be the case after all. I tried adding an If Statement, but that unfortunately didn't work.

Any ideas how to solve this? Thanks for your help!

Answer 1

Why are you making your life difficult... Simply check for vbLf and split by it if it is there:

For Each k In myRange
    If InStr(1, k, vbLf) Then
        SplitText = Split(k, vbLf)
        k.Resize(UBound(SplitText) + 1) = Application.Transpose(SplitText)
    End If
Next