Find last column used on 2 rows and swap rows

[Andy15]

Hi all,

I have some imported data and rows 5 and 6 need swapping, the imported data can contain different amounts of data meaning that the number of columns used can be different.

I have tried the following but can't seem to get it to work

Sub Macro3()


Dim LastCol As Long


With ActiveSheet
        LastCol = .Cells(5, Columns.Count).End(xlToLeft).Column
        Range("A5:LastCol").Select
        Selection.Cut
        Range("A7:LastCol").Select
        Selection.Insert Shift:=xlDown


End With


End Sub

Thanks for any help
Andy

 

[Rick Rothstein]

Does this do what you want...

Sub SwapRows5and6()
  Dim Data As Variant
  Data = Rows("5:6").Value
  Rows(5) = Application.Index(Data, 2, 0)
  Rows(6) = Application.Index(Data, 1, 0)
End Sub

 

[Andy15]

Hi Rick,

Absolutely Brilliant
Thanks so very much
ANdy

 

[Peter_SSs]

Should be able to do it with Cut/Insert as you were trying to do - just do the whole row instead of bothering about the last column

Sub Swap5and6()
  Rows(6).Cut
  Rows(5).Insert
End Sub