Move cells, that meet a criteria, to the top of a list

user_17102542 0
Viewed 44

I am working on a retention tracker in Excel.

There will be 8 columns (month, last name, first name, part time vs full time, hire date, termination date, termination reason, and termination detail) in my table.

I want to pull up all employees who have NOT left the previous month, and add them to the top of the list with their information.

Sub New_month()

Worksheets("Agents").Activate

Dim month As Variant
Dim prev_month As Variant

month = InputBox("Please Enter Month as Abbreviation(i.e, Jan, Feb, etc.)")
rowEnd = Cells(Rows.Count, 1).End(xlUp).Row
previous_month = Cells(2, 1).Value

For i = 1 To rowEnd
    If Cells(i, 1).Value = previous_month And Cells(i, 6).Value = "" Then
        new_row_month = prev_month
        last_name = Cells(i, 2).Value
        first_name = Cells(i, 3).Value
        emp_status = Cells(i, 4).Value
        hire_date = Cells(i, 5).Value
        
        Range("A2").EntireRow.Insert
        
        Cells(2, 1).Value = month
        Cells(2, 2).Value = last_name
        Cells(2, 3).Value = first_name
        Cells(2, 4).Value = emp_status
        Cells(2, 5).Value = hire_date
            
            
        '' Formatting
        Range("A2:H2").Font.Size = 11
        Range("A2:H2").Font.Color = vbBlack
        Range("A2:H2").HorizontalAlignment = xlLeft
        Range("A2:H2").Interior.ColorIndex = 0
        Rows("2:2").RowHeight = 15
        Range("A2:H2").Font.Bold = False
        
    End If
Next i
        
End Sub

The result after code run once:
enter image description here

The result after code run twice:
enter image description here

vba excel
1 Answers

The problem is when you insert a new row the data gets pushed down the sheet and then you loop through looking at the same row over and over. e.g you look at row 2 to start with, insert a new row, the data moves to row 3 and you look at it again etc.

If you add a counter for each new row then it should work

 counter = 0
 For i = 1 To rowEnd
    If Cells(i, 1).Value = previous_month And Cells(i, 6).Value = "" Then
        counter = counter +1
        new_row_month = prev_month
        last_name = Cells(i+counter, 2).Value
        first_name = Cells(i+counter, 3).Value
        emp_status = Cells(i+counter, 4).Value
        hire_date = Cells(i+counter, 5).Value
user_189008430
Related