Swap Values for Entire column

[Devbox777]

Hi guys,

I've been working on a small vba macro that will swap the values in column a and b if column a is greater.

I already came up with the code to swap values between two cells...but I need this code to work for the whole column of a and b. I suppose I need a loop...can someone help me with this? I appreciate it.

here is my existing code.

Sub Transpose()
Dim column1 As Integer
Dim column2 As Integer

column1 = Range("A1").Value
column2 = Range("B1").Value


If column1 > column2 Then

Range("A1").Value = Range("B1").Value
Range("B1").Value = column1

Else: MsgBox "Completed"

End If


End Sub

 

[Gary's Student]

Consider:

Sub Transpose()
   Dim column1 As Integer
   Dim column2 As Integer
   Dim N As Long, i As Long
   N = Cells(Rows.Count, "A").End(xlUp).Row
   
   For i = 1 To N
      column1 = Range("A" & i).Value
      column2 = Range("B" & i).Value
      If column1 > column2 Then
         Range("A" & i).Value = column2
         Range("B" & i).Value = column1
      End If
   Next i
End Sub

 

[Devbox777]

Thank you very much, I will try this out in a few moments.

 

[Devbox777]

Hi,

This code works!! But I do have a couple more conditions I was trying to use in this code as well...in column B..some cells have the word "NULL" is there a way to add an ElseIf statement to copy the value in Column A to B if the value is NULL? I tried to implement it myself and I'm getting errors:

Sub Transpose()

   Dim column1 As Integer
   Dim column2 As Integer
   Dim N As Long, i As Long
   N = Cells(Rows.Count, "A").End(xlUp).Row
   
   For i = 1 To N
      column1 = Range("A" & i).Value
      column2 = Range("B" & i).Value
      
      If column1 > column2 Then
         Range("A" & i).Value = column2
         Range("B" & i).Value = column1
      
      'ElseIf column2 = "NULL" Then
        
        'Range("A" & i).Value = column2
      
      End If
   Next i

2. Also, some of the cells in column B are blank...I want to copy the values of column A to B if this happens as well...

I appreciate the help!

 

[Devbox777]

^^^If someone could help with number 2...I could probably figure out the first part.