Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a worksheet where we need to move all the values matching a pattern to different columns.

For example, we have a B column with values that are one or more letters followed by one or more digits, like k123, k4567, g345, g65535, HJ4567, KL4553, etc. I need to place all values starting with k in column C, all values starting with g in column D, and so on.

Is there any macro to do it?

share|improve this question
    
What have you tried to make this happen? Do you just want the values to show or are you wanting to move them (cut/paste)? Does it have to be a macro? –  CharlieRB Oct 16 '12 at 14:22
    
i want to move them to new columns ansd it has to be a macro because i created a template with button in a cell and when the user presses the button it should automatically to the rest –  william Oct 16 '12 at 14:32
    
On Superuser, to get the best answers the questions need as much detail as possible. You might want to edit your question to include the word move, what you've tried so far and why you want to use a macro (because of the button). –  CharlieRB Oct 16 '12 at 14:40
    
I have a worksheet template where i created a button and assigned couple of macros to it one is splitandtranspose and another one is duplicate values so when i run the button it will just split the data in a column to mulitple rows and then highlight the cell containing duplicates now i need to move all the similar formats starting with Hi or Gi or HJ to a different column so i thought of a macro and if i assign this macro to existing button then it will automatically do the rest thats my thought. I can do it manually but it takes lot of time to do it there are likes 1000's of rows –  william Oct 16 '12 at 14:58
add comment

1 Answer

This code will create a macro to move your data based on the letter preceding the number in each cell of column B. Paste this into the VBA Explorer (Alt+F11) and it will show up in the macro window as MoveData.

You will see the code looks at each cell to see what the left most character is. If its a k the cell contents are copied to the cell 1 place to the right (column C), then clears the original cell. If the character is a g, it is copied to the cell 2 places to the right (column D), then clears the original cell.

Sub MoveData()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("B:B", Range("B:B").End(xlDown))
For Each cell In myrange
    If Left(cell.Value, 1) = "k" Then
        cell.Offset(0, 1).Value = cell.Value
        cell.ClearContents
    ElseIf Left(cell.Value, 1) = "g" Then
        cell.Offset(0, 2).Value = cell.Value
        cell.ClearContents
    End If
Next cell

    'Delete empty cells in column C
    Columns("C:C").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp

    'Delete empty cells in column D
    Columns("D:D").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("A1").Select
End Sub

You can change the values it looks for by modifying the "k" or "g" in the IF statements.

Disclaimer: Use at your own risk. Make a backup copy of your work. Not responsible for any lost data.

share|improve this answer
    
Glad to help. Feel free to mark this as your answer to improve your acceptance rate. –  CharlieRB Oct 16 '12 at 19:47
    
It only moves data across a row and only the data which starts with either k or g, as outlined by your question. If there are values starting with anything else it will leave it in column B. That and blank cells in column B with result in gaps in column C and D. –  CharlieRB Oct 17 '12 at 13:18
    
Updated the code to include the empty cells being removed. –  CharlieRB Oct 17 '12 at 13:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.