I am trying to separate out cells that contain a space from a column. I have tried using the find function but it does not seem to record into 'record macro'.

I am also separating out cells that contain just numbers but using 'find->go to special->Constants(numbers)->insert->shift cells' right works well with 'record macros'

Example

CodePudding user response：

Take a look at this code to see how to set which sheet is processed as well as what range on the sheet you want to process. As written, it will process range A1:A10 on the active sheet.

Sub move_cells_with_space()
    Dim s As Worksheet
    Dim range_to_process As Range
    Dim cell As Range
    
    'choose the sheet to work on
    Set s = ActiveSheet ' use this for the actve sheet
    'Set s = Worksheets("Sheet1") ' use this for a specific sheet
    
    'choose the range to process
    Set range_to_process = s.Range("a1:a10")
    
    For Each cell In range_to_process
        If InStr(1, cell.value, " ") > 0 Then
           cell.Offset(0, 1).value = cell.value
           cell.value = ""
        End If
    Next
    
End Sub