I have a macro, that has been working fine for months until last week when I have started getting a runtime error. My code is below, and the error is highlighted in red? can anyone help??
Sub ConsolidateRows3()
Dim Rw As Long
Dim LR As Long
Dim DelRng As Range
If MsgBox("Consolidate the data on Active sheet?", vbYesNo, "This Sheet?") = vbNo Then Exit Sub
Application.ScreenUpdating = False
'Sheets("RawData").Visible = True
Sheets("RawData").Select
'Copy data sheet
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Consolidated"
'Sort by column A (MachineID)
Range("A1").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, Header:=xlYes
'Sort by column F (PatientID), B (ScheduledStartDate) and M (PlanID)
Range("A1").CurrentRegion.Sort Key1:=Range("F2"), Order1:=xlAscending, _
Key2:=Range("M2"), Order2:=xlAscending, Header:=xlYes
LR = Range("A" & Rows.Count).End(xlUp).Row
Set DelRng = Range("A" & Rows.Count)
'Merge row by row from the bottom up
For Rw = LR To 3 Step -1
'compare B, F and M and only merge if they match
If Range("B" & Rw) = Range("B" & Rw - 1) And _
Range("F" & Rw) = Range("F" & Rw - 1) And _
Range("M" & Rw) = Range("M" & Rw - 1) Then
'Date of Referral
If Range("D" & Rw - 1) = "" Then
Range("D" & Rw - 1) = Int(Range("D" & Rw))
Else
Range("D" & Rw - 1) = Int(Range("D" & Rw - 1))
End If
'Decision to Treat Date
If Range("E" & Rw - 1) = "" Then
Range("E" & Rw - 1) = Int(Range("E" & Rw))
Else
Range("E" & Rw - 1) = Int(Range("E" & Rw - 1))
End If
'DiagCode
If Range("N" & Rw) <> "" Then
If Range("N" & Rw - 1) = "" Then
Range("N" & Rw - 1) = Range("N" & Rw)
Else
Range("N" & Rw - 1) = Range("N" & Rw - 1) & " / " & Range("N" & Rw)
End If
End If
'Tumour Group
If Range("O" & Rw) <> "" Then
If Range("O" & Rw - 1) = "" Then
Range("O" & Rw - 1) = Range("O" & Rw)
Else
Range("O" & Rw - 1) = Range("O" & Rw - 1) & " / " & Range("O" & Rw)
End If
End If
'Modality T
If Range("T" & Rw) <> "" Then
If Range("T" & Rw - 1) = "" Then
Range("T" & Rw - 1) = Range("T" & Rw)
Else
Range("T" & Rw - 1) = Range("T" & Rw - 1) & " / " & Range("T" & Rw)
End If
End If
' 'Modality U
' If Range("U" & Rw) <> "" Then
' If Range("U" & Rw - 1) = "" Then
' Range("U" & Rw - 1) = Range("U" & Rw)
' Else
' If Range("U" & Rw - 1) <> Range("U" & Rw) Then _
' Range("U" & Rw - 1) = Range("U" & Rw - 1) & " / " & Range("U" & Rw)
' End If
' End If
'Flag the row for deletion later
Set DelRng = Union(DelRng, Range("A" & Rw))
End If
Next Rw
'Delete unneeded rows
DelRng.EntireRow.Delete xlShiftUp
Set DelRng = Nothing
'Add new column formulas
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("U2:U" & LR)
.FormulaR1C1 = _
"=IF(RC3="""", """", DATEDIF(RC3,RC2,""d""))"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(AND(LEFT($L1,1)=""Palliative"",$U1>=18,ISNUMBER($U1)), AND(LEFT($L1,1)=""Radical"",$U1>=40,ISNUMBER($U1)))"
.FormatConditions(1).Font.ColorIndex = 3
End With
With Range("V2:V" & LR)
.FormulaR1C1 = _
"=IF(RC5="""", """", DATEDIF(RC5,RC2,""d""))"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(AND(LEFT($L1,1)=""Palliative"",$V1>=18,ISNUMBER($V1)), AND(LEFT($L1,1)=""Radical"",$V1>=40,ISNUMBER($V1)))"
.FormatConditions(1).Font.ColorIndex = 3
End With
'Sort by column A (MachineID)
Range("A1").CurrentRegion.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes
'Cleanup
Rows(1).Font.Bold = True
Columns.AutoFit
Columns("D:E").NumberFormat = "dd/mm/yy"
Range("U2:V" & LR).NumberFormat = "0"
Application.ScreenUpdating = True
Range("A2").Select
ActiveWindow.FreezePanes = True
Sub ConsolidateRows3()
Dim Rw As Long
Dim LR As Long
Dim DelRng As Range
If MsgBox("Consolidate the data on Active sheet?", vbYesNo, "This Sheet?") = vbNo Then Exit Sub
Application.ScreenUpdating = False
'Sheets("RawData").Visible = True
Sheets("RawData").Select
'Copy data sheet
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Consolidated"
'Sort by column A (MachineID)
Range("A1").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, Header:=xlYes
'Sort by column F (PatientID), B (ScheduledStartDate) and M (PlanID)
Range("A1").CurrentRegion.Sort Key1:=Range("F2"), Order1:=xlAscending, _
Key2:=Range("M2"), Order2:=xlAscending, Header:=xlYes
LR = Range("A" & Rows.Count).End(xlUp).Row
Set DelRng = Range("A" & Rows.Count)
'Merge row by row from the bottom up
For Rw = LR To 3 Step -1
'compare B, F and M and only merge if they match
If Range("B" & Rw) = Range("B" & Rw - 1) And _
Range("F" & Rw) = Range("F" & Rw - 1) And _
Range("M" & Rw) = Range("M" & Rw - 1) Then
'Date of Referral
If Range("D" & Rw - 1) = "" Then
Range("D" & Rw - 1) = Int(Range("D" & Rw))
Else
Range("D" & Rw - 1) = Int(Range("D" & Rw - 1))
End If
'Decision to Treat Date
If Range("E" & Rw - 1) = "" Then
Range("E" & Rw - 1) = Int(Range("E" & Rw))
Else
Range("E" & Rw - 1) = Int(Range("E" & Rw - 1))
End If
'DiagCode
If Range("N" & Rw) <> "" Then
If Range("N" & Rw - 1) = "" Then
Range("N" & Rw - 1) = Range("N" & Rw)
Else
Range("N" & Rw - 1) = Range("N" & Rw - 1) & " / " & Range("N" & Rw)
End If
End If
'Tumour Group
If Range("O" & Rw) <> "" Then
If Range("O" & Rw - 1) = "" Then
Range("O" & Rw - 1) = Range("O" & Rw)
Else
Range("O" & Rw - 1) = Range("O" & Rw - 1) & " / " & Range("O" & Rw)
End If
End If
'Modality T
If Range("T" & Rw) <> "" Then
If Range("T" & Rw - 1) = "" Then
Range("T" & Rw - 1) = Range("T" & Rw)
Else
Range("T" & Rw - 1) = Range("T" & Rw - 1) & " / " & Range("T" & Rw)
End If
End If
' 'Modality U
' If Range("U" & Rw) <> "" Then
' If Range("U" & Rw - 1) = "" Then
' Range("U" & Rw - 1) = Range("U" & Rw)
' Else
' If Range("U" & Rw - 1) <> Range("U" & Rw) Then _
' Range("U" & Rw - 1) = Range("U" & Rw - 1) & " / " & Range("U" & Rw)
' End If
' End If
'Flag the row for deletion later
Set DelRng = Union(DelRng, Range("A" & Rw))
End If
Next Rw
'Delete unneeded rows
DelRng.EntireRow.Delete xlShiftUp
Set DelRng = Nothing
'Add new column formulas
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("U2:U" & LR)
.FormulaR1C1 = _
"=IF(RC3="""", """", DATEDIF(RC3,RC2,""d""))"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(AND(LEFT($L1,1)=""Palliative"",$U1>=18,ISNUMBER($U1)), AND(LEFT($L1,1)=""Radical"",$U1>=40,ISNUMBER($U1)))"
.FormatConditions(1).Font.ColorIndex = 3
End With
With Range("V2:V" & LR)
.FormulaR1C1 = _
"=IF(RC5="""", """", DATEDIF(RC5,RC2,""d""))"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(AND(LEFT($L1,1)=""Palliative"",$V1>=18,ISNUMBER($V1)), AND(LEFT($L1,1)=""Radical"",$V1>=40,ISNUMBER($V1)))"
.FormatConditions(1).Font.ColorIndex = 3
End With
'Sort by column A (MachineID)
Range("A1").CurrentRegion.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes
'Cleanup
Rows(1).Font.Bold = True
Columns.AutoFit
Columns("D:E").NumberFormat = "dd/mm/yy"
Range("U2:V" & LR).NumberFormat = "0"
Application.ScreenUpdating = True
Range("A2").Select
ActiveWindow.FreezePanes = True
