Macro help (delete rows based on row value)

Obied70

Board Regular
Joined
Nov 4, 2015
Messages
177
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I need some help from all of you geniuses. I'd like to utilize a macro to delete rows in a spreadsheet based on a repeating set of formats. In the table below, the macro would need to start deleting at the first empty row (2) and finish with deleting the last row "Date/Time Origination." If it makes it easier, the final row is always colored blue. Any help would be great, thanks!
May 18, 2018 5:02:02 PM EDT

<tbody>
</tbody>
blank

<tbody>
</tbody>
blank
Originating or Terminating CUCM End User:

<tbody>
</tbody>
Department: Sales

<tbody>
</tbody>
Outbound Calls: 22

<tbody>
</tbody>
Average Outbound Duration: 00:07:14

<tbody>
</tbody>
Total Outbound Duration: 02:39:27

<tbody>
</tbody>
blank
Call Details

<tbody>
</tbody>
Date/Time Origination

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
To add to this, the number of rows needing deleted will always be 10. So essentially it would be, on first blank cell, delete that row and the following 9 rows.
 
Upvote 0
How about
Code:
Sub DelRws()
With Range("A:A")
   .Replace "Date/Time Origination", "=XXX", xlWhole, , False, , False, False
   .Rows(.SpecialCells(xlBlanks)(1).Row & ":" & .SpecialCells(xlFormulas, xlErrors)(1).Row).EntireRow.Delete
   .Replace "=XXX", "Date/Time Origination", xlWhole, , False, , False, False
End With
End Sub
 
Upvote 0
Seems to be close but it's deleting things it shouldn't be deleting and it's also not running through the entire worksheet
 
Upvote 0
I've edited Fluff's code to look like this
Code:
Sub DelRws()With Range("A:A")
.SpecialCells(xlBlanks).EntireRow.Delete
  
End With
End Sub

Which will delete all blank rows, now I just need delete first blank row then next n number of rows[/SUB]
 
Last edited:
Upvote 0
Can you please re-explain what you are after.
My understanding was that you wanted to delete every row from the 1st blank cell down to the cell containing "Date/Time Origination"
 
Upvote 0
That's correct however, this cycle repeats throughout the worksheet and it seems as though your code stopped after the first instance. My apologies, I didn't explain that well enough.
 
Upvote 0
In post#4 you said
but it's deleting things it shouldn't be deleting
Is this correct? If so can you please explain.
It would also be helpful if you could post some more of your data, so that I can see if there any patterns. Tools to enable you to do this can be found here
 
Upvote 0
There will always be a range of 10 cells that need deleted, starting with first blank cell, ending with Date/Time. The data between that range varies throughout the sheet but anyway I'm having issues getting the add-ins to work, you can disregard thanks.
 
Upvote 0
Okay Fluff my apologies, this macro is working quite well, however if we could modify it to run the entirety of the worksheet (put a loop argument in) that would be amazing.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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
Back
Top