Delete row based on Cell Date

PatriceVeasey

New Member
Joined
Feb 16, 2016
Messages
18
Hi All.

I am looking to create a script that deletes a row based on a date in a particular cell.

I have a list of dates in column F under a table heading "Start Date". The data entry's begin in row 11. What I am looking for is if the dates under this heading are older than 7 days then the VBA will delete the entire row.

I have tried a few scripts however they delete any cell that is blank in column F, therefore deleting almost my entire document. My best guess is that the script would have to include something that will look for the words "Start Date" and then search through all data below this until a blank entry. (I have posted some code below which looks for a heading "Status Average" in a different worksheet and creates a range similar to what I described, however it doesn't do what I want)

Any help would be appreciated!

Thanks in advance.

Code:
   If Cells(j, 3) = "Status Average" Then            
                datarow_start = j + 1
            
                
                Exit For
                
            End If
        
        Next j
        
        If datarow_start <> 1 Then
        
            For g = datarow_start To datarow_start + 50
            
                If Cells(g, 3) = "" Then
                
                On Error Resume Next
                        
                    datarow_end = g - 1
                    Exit For
                    
                End If
            
            Next g
            
            Range(Cells(datarow_start, 3), Cells(datarow_end, 3)).Select
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,267
Hi All.

I am looking to create a script that deletes a row based on a date in a particular cell.

I have a list of dates in column F under a table heading "Start Date". The data entry's begin in row 11. What I am looking for is if the dates under this heading are older than 7 days then the VBA will delete the entire row.

I have tried a few scripts however they delete any cell that is blank in column F, therefore deleting almost my entire document. My best guess is that the script would have to include something that will look for the words "Start Date" and then search through all data below this until a blank entry. (I have posted some code below which looks for a heading "Status Average" in a different worksheet and creates a range similar to what I described, however it doesn't do what I want)

Any help would be appreciated!

Thanks in advance.

Code:
   If Cells(j, 3) = "Status Average" Then            
                datarow_start = j + 1
            
                
                Exit For
                
            End If
        
        Next j
        
        If datarow_start <> 1 Then
        
            For g = datarow_start To datarow_start + 50
            
                If Cells(g, 3) = "" Then
                
                On Error Resume Next
                        
                    datarow_end = g - 1
                    Exit For
                    
                End If
            
            Next g
            
            Range(Cells(datarow_start, 3), Cells(datarow_end, 3)).Select
Hi again Patrice,

If this ties in with your other sorting query, take a look at my latest post there as I think I may have solved this issue for you there.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,504
Messages
5,837,725
Members
430,512
Latest member
sciplants

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