Swap Cells Based On Content

[Nick van Staden]

Looking for an excel VBA that will assist with the following:

if column A has content swap it with content in column B
​

	FPE100​ <strike></strike>
<strike></strike>	FPE101​ <strike></strike>
FPA001​ <strike></strike>	FPO00111
	FPE102​ <strike></strike>
<strike></strike>	FPE103

<tbody>
</tbody>

<strike></strike>
So the response should be:

	FPE100​ ​
<strike></strike><strike></strike>	FPE101​ ​
FPO00111​ ​ <strike></strike>	FPA001​ ​
<strike></strike><strike></strike>	FPE102​ ​
<strike></strike><strike></strike>	FPE103​ ​ <strike></strike>

<tbody>
</tbody>

<strike></strike><strike></strike>

 

[BarryL]

can col B be empty and col A have a value? Can you try the below?

Sub swap()
Dim rng As Range, aStr As String, bStr As String
For Each rng In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
If rng.Value <> "" Then
aStr = rng.Value
bStr = rng.Offset(, 1).Value
rng.Value = bStr
rng.Offset(, 1).Value = aStr
bStr = ""
aStr = ""
End If
Next
End Sub

 

[Peter_SSs]

Try this with a copy of your workbook.

Sub Swap()
  Dim a As Variant, tmp As Variant
  Dim i As Long
  
  With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 2)
    a = .Value
    For i = 1 To UBound(a)
      If Len(a(i, 1)) > 0 Then
        tmp = a(i, 1)
        a(i, 1) = a(i, 2)
        a(i, 2) = tmp
      End If
    Next i
    .Value = a
  End With
End Sub

 

[Nick van Staden]

can col B be empty and col A have a value? Can you try the below?

Sub swap()
Dim rng As Range, aStr As String, bStr As String
For Each rng In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
If rng.Value <> "" Then
aStr = rng.Value
bStr = rng.Offset(, 1).Value
rng.Value = bStr
rng.Offset(, 1).Value = aStr
bStr = ""
aStr = ""
End If
Next
End Sub

Thanks worked 100%