auto move row when complete

Thomas2017

New Member
Joined
Mar 31, 2017
Messages
8
I have a number of cells in each row that have status that can be changed in a drop-down( discharge, active, pending) When I select " discharge" and only discharge, I want the the row moved to either another sheet that says discharges or preferably an area in the same sheet below that has the discharge heading. Is this possible. Please help.
 
Last edited by a moderator:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
In which cell is the discharge heading and will this location of the discharge heading be static?
 
Upvote 0
Below the main sheet starting in A:50 I have a heading of June Discharges. I want the rows to start populating there and below when the macro is triggered. -- below the last text line on the sheet is fine. Current each row has a name/ date of admissions and then columns E:AM are days of the month where I can put a status. If I select discharge from the status let's say in column E ( 1st of the month) I want that row moved to the discharge area below. Hope This helps
 
Upvote 0
I think the VBA CODE you have works but I need to change the complete to discharge and range of from F:F to F: AJ
 
Upvote 0
Try this macro in the worksheet code module:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E:AM")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Target = "discharge" Then
        Target.EntireRow.Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True
End Sub
I'm not sure what you meant by "range of from F:F to F: AJ".
 
Upvote 0
This works perfectly. Thank you so much !!! Last question? What if I want to move to the first row + 1 as opposed to last row and instead of removing entire row I only want to remove the row up until AJ but any text in rows after AJ would stay in place.
 
Upvote 0
You are very welcome. :) I'm not sure if I understood correctly, but give this a try:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E:AM")) Is Nothing Then Exit Sub
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Application.EnableEvents = False
    If Target = "discharge" Then
        If LastRow = 50 Then
            Cells(Target.Row, 1).Resize(, 36).Cut Cells(Rows.Count, "A").End(xlUp).Offset(2, 0)
        Else
            Cells(Target.Row, 1).Resize(, 36).Cut Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    End If
    Application.EnableEvents = True
End Sub
 
Upvote 0
nameadmission datedischarge datejune 1june 2june 3june 4june 5june 6june 7 etc..
discharge

<tbody>
</tbody>

Thank you so much for all your help. I am almost complete. Is there anyway I can populate a discharge date when discharge is entered below the corresponding date of the month. In this scenario it would be June 3- so I would want June 3 to populate into the discharge date field.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top