VBA delete range based on sum value

[EMcK01]

Hi All,

I have varying blocks of data that cover columns A to G with blocks of hours in column D. I have added 2 blank rows to separate the projects, and a sum for each block of hours for any single project in column E, within the first blank row I added. I now need to delete any range where the sum I've added is < 25, but not sure best approach for this to make sure it deletes that block of data.

I tried the below however it only deletes the hours within column D rather that the range.

Sub DeleteProjects()
'adds sum formula for all engineers projects

   Dim rng As Range
   Dim Str As String
   For Each rng In Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      With rng.Offset(rng.Count + 1, 1).Resize(1)
      If rng.Value < 25 Then rng.Delete
      End With
   Next rng
End Sub

any help would be appreciated

 

[Fluff]

How about

Sub DeleteProjects()
'adds sum formula for all engineers projects

   Dim rng As Range
   Dim Str As String
   For Each rng In Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      With rng.Offset(rng.Count + 1, 1).Resize(1)
         If .Value < 25 Then rng.EntireRow.Delete
      End With
   Next rng
End Sub

 

[EMcK01]

Hi Fluff, thanks for the quick response. Unfortunately its not quite working for me, it just appears to be going through the ranges and deleting them rather than checking if the sum of hours for each range that I've added to column E then deleting those less than 25

I don't know if its my If statement thats not defined enough, that should be looking at the sum thats been added to row E for the hours that have been included in row D.

Thanks

 

[Fluff]

The code is looking at col E 2 rows below each block of data in col D. Is that where it should be looking?