Moving entire row to the bottom of the list

[vikramjeet]

Hi,

Is there a way to move an entire row to the bottom of the list if certain value appears in that particular row.

ROW Emp ID Name Mon Tue Wed Thu Fri
1-Nov 2-Nov 3-Nov 4-Nov 5-Nov
1 123 JOHN P P P
2 124 WILLIAM P P P
3 125 TRACY P P A
4 126 TARA P P P
5 127 FRANK P P P
6 128 BILL P P A
7 129 GEORGE P P P
8 130 BRUCE P P P
9 131 GRACE P P P
10 132 SARAH P P P

In this case Tracy and Bill were Absent on a particular day. The entire row 3 and 6 should move to the bottom of the list automatically when "A" is entered in the cell.

 

[Krishnakumar]

Hi,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow    As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
If UCase(Target.Value) = "A" Then
    Application.EnableEvents = False
    Target.EntireRow.Cut
    Rows(lRow + 1).Insert Shift:=xlDown
    Application.EnableEvents = True
End If
End Sub

HTH