How to Rotate a list of values with VBA in Excel

How to Rotate a list of values with VBA in Excel. Change the order of values in Excel. Rotate two lists at the same time. Use the search function. Search for the first part of a value in a list. Excel for Analysts

Sub shuffle()
Dim ws, ws1 As Worksheet
Set ws = Worksheets(“Sheet1”)
Set ws1 = Worksheets(“Sheet2”)
Dim rownumber As Integer
Dim SearchRange As Range
Dim rng, rng1 As Range
Dim FindRow As Range

Set SearchRange = ws.Range(“B5:B49”)

Set FindRow = SearchRange.Find(“Emp”, LookIn:=xlValues, lookat:=xlWhole)

If Not FindRow Is Nothing Then ReturnRowNumber = FindRow.Row
rownumber = FindRow.Row

Set rng = ws.Range(“A5:B5”)

ws.Range(Cells(rownumber, 1), Cells(rownumber, 2)).Insert Shift:=xlDown

Set SearchRange = ws1.Range(“A3:A48”)
Set FindRow = SearchRange.Find(“Emp*”, LookIn:=xlValues, lookat:=xlWhole)

If Not FindRow Is Nothing Then ReturnRowNumber = FindRow.Row
rownumber = FindRow.Row
Set rng1 = ws1.Range(“A3”)
ws1.Range(“A” & rownumber).Insert Shift:=xlDown

End Sub
For more help visit my website or email me at

I can customize this Excel template as it sits for a fixed price of $50. The work is normally returned within 24hrs.

I am able to provide online help on your computer at a reasonable rate.

Check out my next one-hour Excel Webinar

MY videos are all created on Camtasia.

Check out Crowdcast for creating your webinars

If you need to buy Office 2019 follow

Follow me on Facebook

Follow me on twitter

IG @barbhendersonconsulting

I am going to show you how to rotate a list of values with VBA in excel.

I developed this for someone with a schedule with four days on and three days off.
They did not want their employees to have to work the same days every week.
So they wanted to rotate the employees to work a different schedule every month.

We will not spend too much time on the schedule but look at the names of the employees.
Right now we have Tasha Franks on top and he starts working on Sunday.
When we click the Shuffle names button, The Tasha Franks moves to the bottom and Timmy John Will start work on Sunday.
Now, on this work book we have two sheets. The one that you are looking at and the sheet 2 which has the list of employees. Theses sheets are simply linked. We can see that when we click on a name and in the menu bar it displays =sheet2!A3
Now when we rotate the names on sheet 1 we will have to rotate the names on sheet two as well. Notice, as well that on sheet 1, I have a short form of the name which is simply the first 3 letters of their name. These will have to be rotated also
On both sheets, when we do not have any more employees we have the word employee in the list

So let us look at the code, the code will be included in the description of the video.

I begin by defining ws and ws 1 as worksheets, I tell it that ws =sheet1 and ws1 = sheet 2
Then I define rownumber as an integer and then I define a bunch of ranges.

I activate sheet1
And I define the search range as b5 to b49

Now we are defining the second column as the search range, so we are looking at the 3 letters at the first of the name.

So we use the search function and search for EMP which means that we have reached the bottom of the list.
When we find emp then the row of emp is defined as the row number

What we do is select the top row of our range and cut it
And then we go to the bottom of our list and insert the cut cells and shift the rest down

So on the second sheet we do the same thing but note we do not have a cell with emp in it so what we are searching for is EMP with a wild card and so that will search for a value of a cell that starts with emp

Watch more new videos about Excel Office | Synthesized by Mindovermetal English

5/5 - (1 bình chọn)

Bài viết liên quan

Theo dõi
Thông báo của
1 Comment
Cũ nhất
Mới nhất Được bỏ phiếu nhiều nhất
Phản hồi nội tuyến
Xem tất cả bình luận
Khitab Khan