VBA delete range based on sum value

EMcK01

Board Regular
Joined
Jun 14, 2015
Messages
110
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.

VBA Code:
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Board Regular
Joined
Jun 14, 2015
Messages
110
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
The code is looking at col E 2 rows below each block of data in col D. Is that where it should be looking?
 

Forum statistics

Threads
1,141,403
Messages
5,706,252
Members
421,434
Latest member
DaltonB

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
Top