Link to home
Start Free TrialLog in
Avatar of willie108
willie108

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Elton Pascua
Elton Pascua
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of willie108
willie108

ASKER

Hi. Thanks. Could you post the code itself instead of the whole macro sheet?
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

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

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
Avatar of Rob Henson
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
Thanks. Do you think I could record that set of steps in a macro and have it work as a macro?
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