Sub test()
Dim rng As Range, i As Long
Set rng = Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
For i = 1 To rng.Cells.Count Step 2
With rng
If Not IsNumeric(.Cells(i).Value) Then .Cells(i + 1) = .Cells(i).Value
End With
Next
End Sub
Does this do what you want...
Code:Sub test() Dim rng As Range, i As Long Set rng = Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row) For i = 1 To rng.Cells.Count Step 2 With rng If Not IsNumeric(.Cells(i).Value) Then .Cells(i + 1) = .Cells(i).Value End With Next End Sub
Didn't work
Sub My_Sub()
'Modified 8/6/2019 5:27:34 PM EDT
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
If Not IsNumeric(Cells(i, 1).Value) Then Cells(i, 2).Value = Cells(i, 1).Value
Next
End Sub
Sub test2()
Dim arr, i As Long
arr = Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row)
For i = LBound(arr) To UBound(arr)
If Not IsNumeric(arr(i, 1)) Then arr(i, 2) = arr(i, 1)
Next
Range("A1").Resize(UBound(arr, 1), 2) = arr
End Sub
Sub CopyTextOnly()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
Range("B1:B" & LastRow).Value = Range("A1:A" & LastRow).Value
Columns("B").SpecialCells(xlConstants, xlNumbers).Clear
Application.ScreenUpdating = True
End Sub
Yeah, I thought copying all the data over then filtering might slow things down, but I didn't think it would do so that adversely. Thanks for doing the test.Hi Rick,
Ran it twice, ~49 seconds both times.
As a check, ran the other code right after to make sure that the machine was not busy somewhere else and got the same results...