CODEWITHSUNDEEP
excelvbarotationmultiple-columnsmove
Mirel
Mirel

Reputation: 11

VBA code to rotate a list of positions/names on two columns, one row up (top row will fall last) but not to include blank cells

VBA code that can rotate in sync up one row two columns with positions/names so that the top row will always fall down on last position. The code should ignore the blank cells and only rotate the cells with data. Selection of cells is A3-B18 Cells A3 and B3 should rotate to positions A6 and B6. Cells A4 and B4 should move up one row to A3 and B3. Cells A5 and B5 should move up to A4 and B4. Basically, even though the selection is much bigger, only the cells with data should take action and rotate.

Before rotation

enter image description here

After rotation

enter image description here

This is the code:

Range("A9:B9").Select
Selection.Copy
Range("A29").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A10:B24").Select
Application.CutCopyMode = False
Selection.Copy
Range("A9").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, IconFileName:=False
Range("A29:B29").Select
Selection.Copy
Range("A24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False
Range("A29:B29").Select
Application.CutCopyMode = False
Selection.ClearContents Range("A3")

Upvotes: 0

Views: 92

Answers (1)

Black cat
Black cat

Reputation: 6271

A little bit adapted to the specific request:

Sub roller()

lastrow = Range("A2").End(xlDown).Row

Range("A3:B3").Cut Range("A" & lastrow + 1 & ":B" & lastrow + 1)
Range("A3:B3").Delete xlShiftUp


End Sub

Upvotes: 1

Related Questions