0

I need to create a Macro that moves all rows in column "B" after the last cell with data in column "A". One of the challenges is that the amount of data varies so I don't know how many rows there will be in column "B" (need to find through VBA code how many and cut all of them) and also I don't know the last row of column "A".

P:S - the data is ordered so there will be no empty cells until the last row of the column

move columns

Thanks in advance.

braX
  • 11,506
  • 5
  • 20
  • 33
Clau Maj
  • 1
  • 2
    Quick and dirty `Range("B1", Range("B1").End(xlDown)).Cut Range("A1").End(xlDown).Offset(1, 0)` – Daniel Aug 26 '22 at 04:23
  • Tried this and get a compilation error - "Expected: =" – Clau Maj Aug 26 '22 at 15:52
  • The code should all be on one line. – Daniel Aug 26 '22 at 16:44
  • Hi. Did this and worked great. now the problem is that when it gets to an empty column I get "Runtime Error 1004: This selection isn't valid..." - How can I check if there's any data in the column and skip if there isn't? - I assume this would fix the issue? – Clau Maj Aug 26 '22 at 17:23
  • You can throw that line between these 2 lines `If Not IsEmpty(Range("B1")) Then` and `End If` OR just add `If Not IsEmpty(Range("B1")) Then ` in front of the existing line so would be `If Not IsEmpty(Range("B1")) Then Range("B1", Range("B1").End(xlDown)).Cut Range("A1").End(xlDown).Offset(1, 0)` – Daniel Aug 26 '22 at 19:00

1 Answers1

0

The following Macro should do what you want assuming your data always starts in row 1, it will ignore if there is no data in the column

Sub mCutAndPaste()

Dim lRowA, lRowB As Long

Range("A1").Select
Selection.End(xlDown).Select
lRowA = ActiveCell.Row

Range("B1048576").Select
Selection.End(xlUp).Select
lRowB = ActiveCell.Row
If lRowB = 1 Then
Else
    Range("B1:B" & lRowB).Cut
    Range("A" & lRowA + 1).Select
    ActiveSheet.Paste
End If

End Sub

Stuart
  • 144
  • 5
  • Hi. Did this and worked great. now the problem is that when it gets to an empty column I get "Runtime Error 1004: This selection isn't valid..." - How can I check if there's any data in the column and skip if there isn't? - I assume this would fix the issue? – Clau Maj Aug 26 '22 at 17:10
  • Hi @Clay Maj, does the amended code solve the issue? – Stuart Aug 27 '22 at 09:59
  • Allow me some hints as there are quite a lot of beginner traps included: a) not declaring `lRowA` *explicitly* results in a Variant, not in a Long; b) not fully qualifying your Range references returns the currently active sheet, on the other side you overuse `Activate` and `Select` - c.f. [How to avoid ...](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba); c) there are more valid ways to get the last worksheet row than to reference it by an absolute row number of e.g. 1048576 (depends on versions). @Stuart – T.M. Aug 27 '22 at 18:55