• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

clear entries and delete row

Hi, I need a macro which will use control shift down arrow,  and then search within that selection for any cell that has a date value less than June 1, 2012 and delete that row. Can anyone give me some help for that?
The date format is 8/18/2012.

Thank you.
0
willie108
Asked:
willie108
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Elton PascuaCommented:
I hope this serves the purpose. This is a more flexible approach (it asks you for the range and the date) that would make it reusable should the date criteria and date range change.
delete-date-less-than.xlsm
0
 
willie108Author Commented:
Hi. Thanks. Could you post the code itself instead of the whole macro sheet?
0
 
willie108Author Commented:
How about this?
Sub Deleteit()
Dim c As Range
For Each c In Selection
If c.Value = Range("$B$1").Value < 41000 Then c.EntireRow.Delete
Next
End Sub

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Elton PascuaCommented:
With your code, the problem is when looping through a range with each cell as the criteria, as cells get deleted, the range gets modified so it might not delete some cells.

Option Explicit

Sub DeleteLessThan()
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim theDate As Date
    Dim delRange As Range
    Dim cell As Range
    Dim i As Long
    
    Set wb = ThisWorkbook
    Set ws = ThisWorkbook.ActiveSheet
    
    On Error Resume Next
    Set delRange = Application.InputBox(Prompt:="Choose date range.", _
                                        Title:="Date Selection", Type:=8)
    On Error GoTo 0
    If delRange Is Nothing Then Exit Sub
    
    On Error GoTo ErrHandler
    theDate = CDate(Application.InputBox(Prompt:="Enter date (mm/dd/yyyy)", _
                                        Title:="Date Prompt", Type:=2))
    On Error GoTo 0
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For i = 1 To delRange.Cells.Count
        If delRange.Cells(i, 1).Value < theDate Then
            delRange.Cells(i, 1).ClearContents
        End If
    Next i
       
    On Error Resume Next
    delRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
    Exit Sub
    
ErrHandler:
    MsgBox ("Invalid date.")
    
End Sub

Open in new window

0
 
krishnakrkcCommented:
Hi

Not for points. Looping can be avoided in all cells

On Error Resume Next
For i = 1 To delRange.Columns.Count
    delRange.Columns(i).Replace vbNullString, "###", 1
    delRange.Columns(i).Replace theDate, vbNullString
    delRange.Columns(i).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    delRange.Columns(i).Replace "###", vbNullString, 1
Next

Open in new window


Kris
0
 
Rob HensonIT & Database AssistantCommented:
A non VBA option.

Apply AutoFilter to the range. Use the dropdown to filter for dates less than June 1. When you highlight the visible rows and delete them, only those rows will be deleted; the hidden rows will stay. Remove the AutoFilter and required rows remain.

Thanks
Rob H
0
 
willie108Author Commented:
Thanks. Do you think I could record that set of steps in a macro and have it work as a macro?
0
 
Rob HensonIT & Database AssistantCommented:
Yes you can.

When you record the macro you may end up with hardcoded values that you don't want, such as the range filtered and the filter value.

The range can be changed to use CurrentRegion or you can define a dynamic Named Range for the data.

The required filter value could be obtained from an input box.

Thanks
Rob H
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now