select and delete a certain date

[lior03]

hello
i have a piece of accounting data on an excel sheet.some of the data if from the ninth of the month-09/02/2006 or 09/07/2006 to name but a few.
other cells contain data from the tenth of the month- 10/06/2006 or 10/11/2006.
how can i delete al the entire rows of data fro the ninth of the month?
or the tenth ?

Sub deleteinfo()
Dim cell As Range
For Each cell In Columns("c").Rows
If Day(cell) = 9 Then
cell.EntireRow.Delete
End If
Next
End Sub


is there any other solution?
thanks

[malik641]

Hey,

Here's a really fast method to do this (so long as all the rows contain some data in column c):
Public Sub DeleteRowsFast()
' Takes a range of data, loops through all the
' cells, if it meets the criteria to be deleted
' the the cell's value is replaced with ""
' Then at the end, take the original range and delete
' all empty cells.
Application.ScreenUpdating = False
Dim ws As Excel.Worksheet
Dim rngData As Excel.Range
Dim cell As Excel.Range

Dim StartTime As Double
StartTime = Timer

Set ws = ActiveSheet
' Set the range to work with
With ws
Set rngData = .Range(.Cells(1, "C").Address, .Cells(.Rows.Count, "C").End(xlUp).Address)
End With

' Loop through the cells in the range and look for dates with the day being 9
' then replace the cell.value with ""
On Error Resume Next
For Each cell In rngData
If Day(cell.Value) = 9 Then cell.Value = ""
Next
On Error GoTo 0

' Delete entire rows of blank cells in rngData
ws.Range(rngData.Address).Cells.SpecialCells(xlCellTypeBlanks).EntireRow.De lete

Application.ScreenUpdating = True

MsgBox "Time was: " & (Timer - StartTime) & " seconds."
End Sub

For me, 10,000 rows --> 0.4 seconds :cool:

KB pending, BTW.

[lior03]

hello
i want to rid the user from the need to remember what is the last cell in column c with a date .so i thought about using a name to define the range of cell i am working on.

Sub NameMyRange()
Dim cell As Range
Dim MyRange As Name
Range("C1", Range("C65536").End(xlUp)).Name = "MyRange"
For Each cell In MyRange
If Day(cell) = 9 Then
cell.EntireRow.Delete
End If
Next
End Sub


how can i get excel do the job for me?
thanks

[malik641]

Hey,

Why do they need to remember in the first place?


And why not just teach them how to use Ctrl+Down_Arrow while in Column C.



And if you look at my code, I created a range to work with:
' Set the range to work with
With ws
Set rngData = .Range(.Cells(1, "C").Address, .Cells(.Rows.Count, "C").End(xlUp).Address)
End With

[geekgirlau]

I've never had an occasion to use "Cells.SpecialCells(xlCellTypeBlanks)" - normally I would use an AutoFilter to display the values to be deleted, then use SpecialCells(xlCellTypeVisible) to delete the visible rows.

I don't know whether this would be faster, just another way to skin a cat (please note: no cats were harmed in the typing of this post).

[lior03]

hello
i want to generelize and let the user use the macro without specifing the column.

Sub deleteinfo1()
Dim cell As Range
Dim x As Integer
On Error GoTo err
If IsEmpty(ActiveCell) Then Exit Sub
x = InputBox("specify a day of the month to erase:")
For Each cell In ActiveCell.EntireColumn.Activate
If Day(cell) = x Then
cell.EntireRow.Delete
End If
Next
Exit Sub
err:
MsgBox "no valid entry given", vbCritical
End Sub


can i use the macro without metioning the column?
thanks

[malik641]

I've never had an occasion to use "Cells.SpecialCells(xlCellTypeBlanks)" - normally I would use an AutoFilter to display the values to be deleted, then use SpecialCells(xlCellTypeVisible) to delete the visible rows.

I don't know whether this would be faster, just another way to skin a cat (please note: no cats were harmed in the typing of this post).
I will definitely note that method. Sounds faster than what I've been using; that is, when you have values that you know you want to delete. I also use my method for deleting values that don't equal the given values. I'm not sure I could implement your method with that situation. What do you use in that case?

[geekgirlau]

Off the top of my head I would probably have a calculated filter field, that displays a cryptic message like "DELETE" - something like

"=IF(ISNA(VLOOKUP(A1,MyLookupRange,1,FALSE)),"DELETE","")

The AutoFilter then looks for "DELETE" in that field, and you delete the visible cells.

One thing here I forgot to mention - if you delete the visible cells in a named range, make sure the range does NOT include the headings, otherwise they'll be deleted as well.

[malik641]

Off the top of my head I would probably have a calculated filter field, that displays a cryptic message like "DELETE" - something like

"=IF(ISNA(VLOOKUP(A1,MyLookupRange,1,FALSE)),"DELETE","")

The AutoFilter then looks for "DELETE" in that field, and you delete the visible cells.

One thing here I forgot to mention - if you delete the visible cells in a named range, make sure the range does NOT include the headings, otherwise they'll be deleted as well. Seems like that would be a little more work if you're going to create the formula via code on-the-fly, insert it into all the rows that you want (also have to find an open column or just insert one and delete it later) then perform an autofilter ... then delete the rows ... remove autofilter ... remove formula column.

Come to think of it, I should just use Advanced Filter to filter the items not equal to the ones I have to only keep the known values. Hmmm....:think:
I'll do some test and see which one is faster this weekend. :)





hello
i want to generelize and let the user use the macro without specifing the column.



VBA:


Sub deleteinfo1()
Dim cell As Range
Dim x As Integer
On Error Goto err
If IsEmpty(ActiveCell) Then Exit Sub
x = InputBox("specify a day of the month to erase:")
For Each cell In ActiveCell.EntireColumn.Activate
If Day(cell) = x Then
cell.EntireRow.Delete
End If
Next
Exit Sub
err:
MsgBox "no valid entry given", vbCritical
End Sub

can i use the macro without metioning the column?
thanks
You should check if x is a number or not using IsNumeric(x) incase the user enters text rather than numbers. And about specifying the column, you can either ask the user to specify the column or you can keep an enumeration of all the column numbers in use on that particular worksheet and whenever there is a change to the worksheet, just adjust the Enumeration...something like:

Public Enum shDataCols
eDate = 1
eName
eItemPurchased
ePrice
End Enum

Effectively, eDate's value is 1 and everything under it (unless explicitly given a value) is 1 incremented from the previous. If you insert a new column in the sheet that the code uses, just insert another variable in the Enumeration. Pretty nice stuff, I think. Here's how I use it to refer to the column to work with (using the Date column):

Public Sub Test()
Dim rng As Excel.Range

With Sheet1
Set rng = .Range(.Cells(1, shDataCols.eDate), _
.Cells(Rows.Count, shDataCols.eDate).End(xlUp))
End With

Debug.Print rng.Address
End Sub
So if you end up inserting another column in the sheet, and then in the enumeration, the code will work just as before (I Love this method for my projects so far!! :cloud9:).

[malik641]

geekgirlau,

Thanks for the insight on using filters :) I've made a new procedure that's about 4x as fast as my last procedure :thumb


Option Explicit

Public Sub DeleteRowsFast()
' Takes a range of data, loops through all the
' cells, if it meets the criteria to be deleted
' the the cell's value is replaced with ""
' Then at the end, take the original range and delete
' all empty cells.
Application.ScreenUpdating = False
Dim ws As Excel.Worksheet
Dim newWS As Excel.Worksheet
Dim rngData As Excel.Range
Dim cell As Excel.Range
Dim strCriteria As String
Dim StartTime As Double

StartTime = Timer

Set ws = ThisWorkbook.Worksheets("Sheet1")
strCriteria = "=DAY(C2)=9"

' Set the range to work with
With ws
Set rngData = .Range(.Cells(1, "C").Address, _
.Cells(.Rows.Count, "C").End(xlUp).Address)
End With

' Create new worksheet to place criteria on
Set newWS = ThisWorkbook.Worksheets.Add
' Place criteria
newWS.Range("A2").Value = strCriteria

' Now use Advanced filter to show only dates with 9 in it
rngData.AdvancedFilter xlFilterInPlace, newWS.Range("A1:A2")

' Delete entire rows of visible cells in rngData (excluding header row)
rngData.Offset(1, 0).Resize(rngData.Rows.Count) _
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete

' Show all records
ws.ShowAllData

' Delete the created worksheet
Application.DisplayAlerts = False
newWS.Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True
MsgBox "Time was: " & (Timer - StartTime) & " seconds."
End Sub
10,000 rows --> ~0.185 seconds :cool:

[geekgirlau]

Big fan of filters myself - I prefer to use the in-built functions whenever possible rather than reinventing the wheel :hammer: