CODEWITHSUNDEEP
vbaexcel
SIM
SIM

Reputation: 22440

Unable to rectify my script to skip the blank cells

I'm trying to get the second last portion of a big string (separated by space) using vba. My script is doing fine until it hits a blank cell. When any cell contains nothing then it breaks throwing an error Subscript out of range. How can I modify my script in such a way so that It will check first whether any cell is blank. If it finds any blank cell then it will skip that and look for the next cell until the last row?

This is what I've tried so far. It works smoothly until it encounters a blank cell.

Sub splitting_items()
    Dim lrow As Long, cel As Range
    Dim i As Long, item_list As Variant

    lrow = Range("A" & Rows.Count).End(xlUp).Row
    For Each cel In Range("A1:A" & lrow)
        item_list = Split(cel, " ")
        i = UBound(item_list)
        Debug.Print item_list(i - 1)
    Next cel
End Sub

Applied on the below strings. The leading numbers are the row number with 3 and 4 blank.

1 BEBEE TABERNACLE CHURCH CME
2 OCHOA VICTORIANO RLT
3
4
5 MIDCON TEXAS PIPELINE CORP
6 STD DALLAS ST STE

Upvotes: 0

Views: 102

Answers (2)

SIM
SIM

Reputation: 22440

Finally, I've found a workaround. If I try like below, It can handle blank rows and always places the parsed values in it's appropriate position, I meant next to the appropriate strings.

Sub splitting_items()
    Dim cel As Range, i As Long, item_list As Variant

    For Each cel In Sheet1.UsedRange.Columns(1).SpecialCells(xlCellTypeConstants)
        item_list = Split(cel, " ")
        i = UBound(item_list)
        cel(1, 2) = item_list(i - 1)
    Next cel
End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Try testing the value:

Sub splitting_items()
    Dim lrow As Long, cel As Range
    Dim i As Long, item_list As Variant

    lrow = Range("A" & Rows.Count).End(xlUp).Row
    For Each cel In Range("A1:A" & lrow)
        If InStr(1, cel.Value, " ") <> 0 Then
            item_list = Split(cel, " ")
            i = UBound(item_list)
            Debug.Print item_list(i - 1)
        End If
    Next cel
End Sub

Upvotes: 2

Related Questions