Rotating list.

[CTN]

I am trying to create a "call board". Column A will be a check box. Column B will be an employee's name. Column C will be notes about their availability. What I would like for the template to do is, when a check box is checked, the entire row will be moved to the bottom of the board and all rows above will be moved up. This is to help us to be more fair in offering work to on call employees. I will need to be able to modify the list and add/delete names, and will need to be able to move names to the bottom at will, so I can't have a limit on the number of rows. I don't even know where to start on this. If anyone has any ideas, I'd love to read them.

 

[zero269]

I am trying to create a "call board". Column A will be a check box. Column B will be an employee's name. Column C will be notes about their availability. What I would like for the template to do is, when a check box is checked, the entire row will be moved to the bottom of the board and all rows above will be moved up. This is to help us to be more fair in offering work to on call employees. I will need to be able to modify the list and add/delete names, and will need to be able to move names to the bottom at will, so I can't have a limit on the number of rows. I don't even know where to start on this. If anyone has any ideas, I'd love to read them.

I think I would first start creating some sample data layed out the way you describe.
From there, ask youself how the data will be populated and if any columns would need any drop-down lists or formulas to lookup or calucate the data.
However, as it relates to the idea of a checkbox that causes rows to MOVE... Now your talking about a Macro-enabled Workbook. In other words, VBA.
There are plenty of professionals in this forum that can offer better advice than me. But for now, get some sample data drawn up and then use the forums Excel Add-In to share it. Take your time on that page to learn how to use it. It will benefit you greatly from here on.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Screenshots don't go over well, becasue no one wants to type it up on their own...

 

[Logit]

The following should perform as requested. Tested working here.

Sub RotList()
    Dim rngA As Range, rngB As Range, rngLeft As Range
    Dim lastCol As Long, Rws As Long
    Application.ScreenUpdating = False
    
    If Range("A2") Is Nothing Then Exit Sub
    
    With Sheets("Sheet1")
        ' Find the last column with data in row 2 (left of Column A)
        lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        
        ' Define the range for the unique terms in Column A
        Set rngA = .Range(.Cells(2, 1), .Cells(6, 1)) '<--- edit range for more names
        
        ' Define the range for the corresponding terms in Column B
        Set rngB = .Range(.Cells(2, 2), .Cells(6, 2)) '<--- edit range for corresponding terms
        
        ' Define the range for columns to the left of Column A that have data
        If lastCol > 2 Then
            Set rngLeft = .Range(.Cells(2, 3), .Cells(6, lastCol)) ' Range to the left of Column A
        Else
            Set rngLeft = Nothing ' No columns to the left if no data exists
        End If
        
        Rws = rngA.Rows.Count
        
        ' Move the first row in all columns (A, B, and left) to the last row
        rngA.Rows(1).Cut Destination:=rngA.Rows(Rws).Offset(1)
        rngB.Rows(1).Cut Destination:=rngB.Rows(Rws).Offset(1)
        If Not rngLeft Is Nothing Then rngLeft.Rows(1).Cut Destination:=rngLeft.Rows(Rws).Offset(1)
        
        ' Move the second row in all columns (A, B, and left) to row 1
        rngA.Rows(2).Cut Destination:=rngA.Rows(1)
        rngB.Rows(2).Cut Destination:=rngB.Rows(1)
        If Not rngLeft Is Nothing Then rngLeft.Rows(2).Cut Destination:=rngLeft.Rows(1)
        
        ' Delete the second row in all columns (A, B, and left)
        rngA.Rows(2).Delete shift:=xlUp
        rngB.Rows(2).Delete shift:=xlUp
        If Not rngLeft Is Nothing Then rngLeft.Rows(2).Delete shift:=xlUp
      
    End With
    Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

The following should perform as requested.

Should it?
Seems no choice about which row is moved down. No mention in post 1 of always moving row 2 down.

Also seems to not allow for this unless the code is to be altered every time names are added or deleted

I will need to be able to modify the list and add/delete names, and will need to be able to move names to the bottom at will, so I can't have a limit on the number of rows

@CTN
Welcome to the MrExcel board!

and all rows above will be moved up.

Don't you mean all rows below will be moved up?
If not more explanation please.

If you did mean all rows below, then my suggestion would be to get rid of the checkboxes. They are not needed and would add complication when adding/deleting names in the list.
Instead, my suggestion to move somebody to the bottom of the list would be to double-click their name in column B after installing the double-click code given below as follows.
(Test with a copy of your workbook)

1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by double-clicking names in column B. (I have assumed a heading row 1 and names starting in row 2)
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column = 2 And Target.Row > 1 And Len(Target.Value) > 0 Then
    Cancel = True
    Application.ScreenUpdating = False
    Target.EntireRow.Copy Destination:=Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
    Target.EntireRow.Delete
    Application.ScreenUpdating = True
  End If
End Sub

 

[Logit]

Peter_SSs​

Nice macro. Great work !

 

[CTN]

Should it?
Seems no choice about which row is moved down. No mention in post 1 of always moving row 2 down.

Also seems to not allow for this unless the code is to be altered every time names are added or deleted



@CTN
Welcome to the MrExcel board!


Don't you mean all rows below will be moved up?
If not more explanation please.

If you did mean all rows below, then my suggestion would be to get rid of the checkboxes. They are not needed and would add complication when adding/deleting names in the list.
Instead, my suggestion to move somebody to the bottom of the list would be to double-click their name in column B after installing the double-click code given below as follows.
(Test with a copy of your workbook)

1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by double-clicking names in column B. (I have assumed a heading row 1 and names starting in row 2)
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column = 2 And Target.Row > 1 And Len(Target.Value) > 0 Then
    Cancel = True
    Application.ScreenUpdating = False
    Target.EntireRow.Copy Destination:=Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
    Target.EntireRow.Delete
    Application.ScreenUpdating = True
  End If
End Sub

Yes, sorry. All rows below will move up on the list. My apologies.

 

[Peter_SSs]

Yes, sorry. All rows below will move up on the list. My apologies.

No problem. So, did you try the code?

 

[CTN]

No problem. So, did you try the code?

You, sir, are a genius! I just got back into the office and tried the code, it works beautifully!
Thank you so very much! You have successfully cut my maintenance of this list by 80%!!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.