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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,046
Messages
5,639,757
Members
417,108
Latest member
Thein Than

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