Cut and paste text cells from one column to another

Question

I'm essentially looking to move any cells with text from one of two columns across from column E/F to A. Keeping all other contents of the row the same. Have done and found examples with specific strings but nothing with just text. I think my issue maybe in the formatting of cell.value = IsText. Also not sure how to set the range to the last row in the column/sheet containing data. Other examples do entire sheet (even blanks) which can be slow at times.

Sub MoveJobFunctions()
    Dim row As Long

    For row = 2 To 1000
        'Check if is text
        If Range("E" & row).Value = IsText Then
            ' Copy the value and then blank the source.
            Range("A" & row).Value = Range("E" & row).Value
        End If
    Next
End Sub

Evil Blue Monkey · Accepted Answer · 2022-06-11T08:20:28.887

The code will read your IsText as an undeclared variable. Therefore the code will check if the value in column E is "equal to nothing". This code should do the trick:

Option Explicit
Sub MoveJobFunctions()
    Dim row As Long

    For row = 2 To 1000
        'Check if is text
        If Excel.WorksheetFunction.IsText(Range("E" & row).Value) And Not IsDate(Range("E" & row).Value) Then
            ' Copy the value and then blank the source.
            Range("A" & row).Value = Range("E" & row).Value
            Range("E" & row).ClearContents
        End If
    Next
End Sub

As you can see, Excel.WorksheetFunction.IsText the proper way to use the IsText function. I've added the Option Explicit statement that will require you to declare any variable (or an error will occur). It will help you spot errors like the one that made your code uneffective. I've also added a line to clear the source value (as specified in your code's comment).

Note added after new comments: i've added an IsDate function to check if the value is a date. IsDate is a function stored in the VBA.Information library. Since it's a VBA function, you don't need to specify the entire address as for the IsText function which is a Excel function (and it's stored in Excel.WorksheetFunction library). More information about IsDate here.

In order to cover only the list and not the whole sheet (or an hardcoded number of rows) you can use some trick like the ones in this link. A possible code (if there is nothing under the list) could be this one:

Option Explicit
Sub MoveJobFunctions()
    Dim row As Long
    Dim lastrow As Long
    
    lastrow = Range("E" & Cells.Rows.Count).End(xlUp).row
    
    For row = 2 To lastrow
        'Check if is text
        If Excel.WorksheetFunction.IsText(Range("E" & row).Value) And Not IsDate(Range("E" & row).Value) Then
            ' Copy the value and then blank the source.
            Range("A" & row).Value = Range("E" & row).Value
            Range("E" & row).ClearContents
        End If
    Next
End Sub

In case the list is an uninterrupted series of data, you could use this code:

Option Explicit
Sub MoveJobFunctions()
    Dim row As Long
    Dim lastrow As Long
    
    lastrow = Range("E2").End(xlDown).row
    
    For row = 2 To lastrow
        'Check if is text
        If Excel.WorksheetFunction.IsText(Range("E" & row).Value) And Not IsDate(Range("E" & row).Value) Then
            ' Copy the value and then blank the source.
            Range("A" & row).Value = Range("E" & row).Value
            Range("E" & row).ClearContents
        End If
    Next
End Sub

Or this one:

Option Explicit
Sub MoveJobFunctions()
    
    Dim cell As Range
    
    Set cell = Range("E2")
    
    Do Until cell.Value = ""
        'Check if is text
        If Excel.WorksheetFunction.IsText(cell.Value) And Not IsDate(cell.Value) Then
            ' Copy the value and then blank the source.
            Range("A" & cell.row).Value = cell.Value
            cell.ClearContents
        End If
        'Offsetting cell down to the next row.
        Set cell = cell.Offset(1, 0)
    Loop
End Sub

Glad to read that. If the answer satisfies you, you may mark it as accepted. — Evil Blue Monkey, Jun 10 '22 at 13:36
Sorry didnt scroll all the way through the sheet but there is a slight problem. There are cells in the range that are dd/mm/yy that I do not want pasted into A but are still being moved. The excel formula picks it up but the code does not... either that or I would have to add a condition to the pasting? — kca062, Jun 10 '22 at 15:44
I've edited the code accordingly to your last request and added a note to the answer. Anything else i can do for you? — Evil Blue Monkey, Jun 11 '22 at 08:21

Cut and paste text cells from one column to another

1 Answers1