Microsoft Office Forums - View Single Post - [Solved] Spliting column with text and numbers

Boatwrenchv8 · 04-03-2012, 07:27 PM

Hello. This is one way of splitting the columns with text and numbers. It isn't the best or the only way but given your sample data, I have accomplished it. A macro enabled workbook is attached at the bottom of this post. The steps are included on the sheets, please start at Sheet Number 1. The code that is in the workbook is also included below if you need it.

Code:

Sub CleanUpVer1()
'
' CleanUpVer1 Macro
' IMPORTANT:  Start in Cell A1
'
'This macro will start in cell A1
'   and check each Row 's A and B cell's
'   value.  If Cell A and Cell B are the
'   same, the macro will delete that row
'   of data.  If Cell A and Cell B are
'   different, the macro will skip to the
'   next row and continue to check each
'   value until both Cell A and Cell B
'   are blank.
'
' Keyboard Shortcut: Ctrl+w
'
    Dim ItemInCellA As Variant
    Dim ItemInCellB As Variant
 
    Do
 
        ItemInCellA = Selection
        ActiveCell.Offset(0, 1).Range("A1").Select
        ItemInCellB = Selection
 
            If ItemInCellA = ItemInCellB Then
               Selection.EntireRow.Delete
               ActiveCell.Offset(0, -1).Range("A1").Select
            Else
               ActiveCell.Offset(1, -1).Range("A1").Select
            End If
 
    Loop While (ItemInCellA <> "") And (ItemInCellB <> "")
 
End Sub

Rich