VBA help: Removing Patient from list that has Discharged, and for non-discharged getting the time period

[amyap]

Hi all,

I'm trying to write something that would help make my work easier.

1) Trying to remove the row of patient that have discharged.
2) Getting the date time start and end in that ward.

Is it possible? I'm really new to VBA and i'm not sure how to tackle this.

Thanks so much

Here's how it looks like:

S/N	ID.	Name	Case Relationship	Movement Start Date	Movement Start Time	Movement End Date	Movement End Time	Ward	Bed
1​	P0001	Alan A	Inpatient	05.04.2024	16:32:40​	06.04.2024	2:42:47​
2​	P0001	Alan A	Inpatient	06.04.2024	2:42:47​	08.04.2024	14:36:08​	W10	W1002
3​	P0001	Alan A	Inpatient	08.04.2024	14:36:08​	31.12.9999	24:00:00​	W10	W1002
4​	P0002	Brian B	Inpatient	09.04.2024	12:30:02​	09.04.2024	14:23:48​
5​	P0002	Brian B	Inpatient	09.04.2024	12:53:00​	09.04.2024	12:53:00​
6​	P0002	Brian B	Inpatient	09.04.2024	14:23:48​	10.04.2024	21:05:00​	W20	W2001
7​	P0002	Brian B	Inpatient	10.04.2024	21:05:00​	12.04.2024	20:29:05​	W20	W2001
8​	P0002	Brian B	Inpatient	12.04.2024	20:29:05​	23.04.2024	15:19:15​	W10	W1003
9​	P0002	Brian B	Discharged	23.04.2024	15:19:15​	23.04.2024	15:19:15​	W10	W1003
10​	P0003	Charles C	Inpatient	22.04.2024	16:20:58​	22.04.2024	17:12:52​
11​	P0003	Charles C	Inpatient	22.04.2024	17:12:52​	23.04.2024	9:31:47​	W30	W3006
12​	P0003	Charles C	Inpatient	23.04.2024	9:31:47​	23.04.2024	16:29:16​	W30	W3006
13​	P0003	Charles C	Inpatient	23.04.2024	16:29:16​	31.12.9999	24:00:00​	W10	W1003
14​	P0004	Dave D	Inpatient	30.03.2024	17:22:56​	30.03.2024	20:12:07​
15​	P0004	Dave D	Inpatient	30.03.2024	20:12:07​	01.04.2024	20:42:01​	W30	W3008
16​	P0004	Dave D	Inpatient	01.04.2024	20:42:01​	03.04.2024	21:25:56​	W40	W4001
17​	P0004	Dave D	Inpatient	03.04.2024	21:25:56​	05.04.2024	16:07:31​	W10	W1001
18​	P0004	Dave D	Inpatient	05.04.2024	16:07:31​	11.04.2024	21:30:54​	W10	W1001
19​	P0004	Dave D	Inpatient	11.04.2024	21:30:54​	31.12.9999	24:00:00​	W10	W1001

 

[eiloken]

Hi all,

I'm trying to write something that would help make my work easier.

1) Trying to remove the row of patient that have discharged.
2) Getting the date time start and end in that ward.

Is it possible? I'm really new to VBA and i'm not sure how to tackle this.

Thanks so much

Here's how it looks like:

S/N	ID.	Name	Case Relationship	Movement Start Date	Movement Start Time	Movement End Date	Movement End Time	Ward	Bed
1​	P0001	Alan A	Inpatient	05.04.2024	16:32:40​	06.04.2024	2:42:47​
2​	P0001	Alan A	Inpatient	06.04.2024	2:42:47​	08.04.2024	14:36:08​	W10	W1002
3​	P0001	Alan A	Inpatient	08.04.2024	14:36:08​	31.12.9999	24:00:00​	W10	W1002
4​	P0002	Brian B	Inpatient	09.04.2024	12:30:02​	09.04.2024	14:23:48​
5​	P0002	Brian B	Inpatient	09.04.2024	12:53:00​	09.04.2024	12:53:00​
6​	P0002	Brian B	Inpatient	09.04.2024	14:23:48​	10.04.2024	21:05:00​	W20	W2001
7​	P0002	Brian B	Inpatient	10.04.2024	21:05:00​	12.04.2024	20:29:05​	W20	W2001
8​	P0002	Brian B	Inpatient	12.04.2024	20:29:05​	23.04.2024	15:19:15​	W10	W1003
9​	P0002	Brian B	Discharged	23.04.2024	15:19:15​	23.04.2024	15:19:15​	W10	W1003
10​	P0003	Charles C	Inpatient	22.04.2024	16:20:58​	22.04.2024	17:12:52​
11​	P0003	Charles C	Inpatient	22.04.2024	17:12:52​	23.04.2024	9:31:47​	W30	W3006
12​	P0003	Charles C	Inpatient	23.04.2024	9:31:47​	23.04.2024	16:29:16​	W30	W3006
13​	P0003	Charles C	Inpatient	23.04.2024	16:29:16​	31.12.9999	24:00:00​	W10	W1003
14​	P0004	Dave D	Inpatient	30.03.2024	17:22:56​	30.03.2024	20:12:07​
15​	P0004	Dave D	Inpatient	30.03.2024	20:12:07​	01.04.2024	20:42:01​	W30	W3008
16​	P0004	Dave D	Inpatient	01.04.2024	20:42:01​	03.04.2024	21:25:56​	W40	W4001
17​	P0004	Dave D	Inpatient	03.04.2024	21:25:56​	05.04.2024	16:07:31​	W10	W1001
18​	P0004	Dave D	Inpatient	05.04.2024	16:07:31​	11.04.2024	21:30:54​	W10	W1001
19​	P0004	Dave D	Inpatient	11.04.2024	21:30:54​	31.12.9999	24:00:00​	W10	W1001

this sub to delete "Discharged" patient:

Sub test()
    Dim cll As Range, rng As Range
    Set rng = ActiveSheet.Range("D2:D" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row)
    For Each cll In rng
        If cll.Text = "Discharged" Then cll.EntireRow.Delete (xlUp)
    Next cll
End Sub

about "Getting the date time start and end in that ward.", i don't understand it, please explain