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.
The date format is 8/18/2012.
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Hi
Not for points. Looping can be avoided in all cells
Kris
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
Kris
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
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
ASKER
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
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
ASKER