Now, I have written another macro which will work on the Excel ranges. This macro is completely different. My original macro was based on area selection. But the new macro is based on the ranges. It is very small and easy to understand, unlike another macro, which is comparatively bigger.
I will explain each of the lines in the macro.
Check out my youtube channel for more amazing excel and office automation videos
1. Sub swap_2_ranges()
2. string_all_selected_area = (Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False))
The first line is the subroutine name. The second line gets the string of value of all the ranges selected. The output will be "I23,H25,G22" format. It says 3 ranges are selected. Each range is separated by a comma. I will use this string later to get the ranges.
3. array_selected_area = Split(string_all_selected_area, ",")
The above line splits the multiple ranges into one range and stores them in an array
4. If (UBound(array_selected_area) = 1) Then
5. area1 = array_selected_area(0)
6. area2 = array_selected_area(1)
Then, there is an if condition to check if the selection is suitable for our swap operation. It will not work as expected if we select more than 2 ranges. or less than 2 ranges. Our purpose is to swap or interchange the content of 2 different ranges. So the selection should be exactly 2 ranges.
After this check, the selected ranges are stored in 2 temporary variables.
7. selection_1 = Range(area1)
8. selection_2 = Range(area2)
The variables are converted to Excel ranges. This is very important, as we are not getting selection directly in the Excel range format. Instead, the selection is in text format. The string values should be converted into an Excel range object so that we can apply some Excel VBA functionalities.
9. If Range(area1).Count = Range(area2).Count Then
Then comes one more if condition to check if the selected ranges have the same number of cells. It is obvious that we should have the same number of cells on each selection so that we can interchange their values.
10. Sheets(1).Range(area2) = selection_1
11. Sheets(1).Range(area1) = selection_2
This is actual swapping code. This is achieved through the temporary variables.
12. End If
13. End If
14. End Sub
Finally ending the if conditions and the Subroutine.