[cjsmith22]

hi, i have an excel worksheet of hospital patients(columns-patient,date referred,date received,date registered,date admitted,ward,diagnosis).Not all of the patients in the sheet have been admitted(ie have nothing in their date admitted column) How do i extract only the rows where they have been admitted(eg. 1:5,7,9,10:13 etc) into a new sheet? the problem is this must work for any similar set of data (ie. the number of patients can vary).
any help would be greatly appreciated.

[coderitr]

Try testing if date admitted is not null

[Kelly_in_LA]

See if this will work for you...

NOTE: I have assumed (based on your description) that "Date Admitted" is found in column E, which is also column 5 for the sake of my macro.

When you run this macro, make sure that the sheet with the data is your "Active Sheet." Otherwise, the macro will operate on whichever sheet Excel thinks is the active one. In that case, the rows pasted to the new sheet will not be the rows you were expecting.

Sub GetOnlyThoseAdmitted()

'IF THE "DATE ADMITTED" COLUMN IS NOT THE FIFTH COLUMN,
'THEN PLEASE CHANGE THE FOLLOWING LINE:
Const DateAdmittedColumn = 5

Dim myRow As Range
Dim mySourceSheet As Worksheet
Dim myNewSheet As Worksheet
Dim TimeString As String
Dim r As Long

Set mySourceSheet = ActiveSheet
Set myNewSheet = ActiveWorkbook.Sheets.Add

'The following loop gives the new sheet a name.
'It also makes sure that the name we are attempting
'to give the sheet does not already exist.
Do
    On Error Resume Next
    TimeString = Replace(CStr(VBA.Time), ":", ".")
    myNewSheet.Name = "New Extracted Data " & TimeString
Loop While Err.Number = 1004


For Each myRow In mySourceSheet.UsedRange.Rows

    If Trim(CStr(myRow.Cells(1, DateAdmittedColumn).Value)) <> "" Then
    
        myRow.Copy
        r = r + 1
        myNewSheet.Cells(r, 1).Select
        myNewSheet.Paste
    
    End If

Next

Application.CutCopyMode = False
End Sub

[cjsmith22]

All sorted - much appreciated