Solved: Delete records for the current month

[expfresocspy]

How do you write a VBA coding to filter a column filled with dates and delete any records that are for the current month ? Eg: This month is January 2012 so the list of dates in the column should not have any records for the month of January 2012 .

Thank you for the help !

[CharlesH]

Hi,

The following code may help. It will filter column A for the Date. The column must be formatted "mmmm" which shows only the Month.

[vba]
Sub DelMonth()
''' This looks at column A for the month only
Dim HowManyVisRows As Long
Dim VisRng As Range
Dim Mymonth As String
Mymonth = Format(Now, "mmmm") '''set to see the mont oly
'apply the filter someway
Dim iCtr As Long
Range("A1").Select
Selection.AutoFilter
With Selection
.AutoFilter Field:=1, Criteria1:=Mymonth '' this set the filtered data for the value
End With
With Worksheets("Sheet2").AutoFilter.Range
'subtract one for the header.
HowManyVisRows _
= .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Cells.Count - 1

If HowManyVisRows >= 2 Then
'avoid the header and come down one row
'and only look at one the first column
Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
VisRng.Offset(1, 0).Select
With ActiveCell
Range(Cells(ActiveCell.Row, 1), Cells(Range("A65536").End(xlUp).Row, 4)).EntireRow.Delete
End With

End If
End With

End Sub
[/vba]

[mdmackillop]

[VBA]Option Explicit
Sub Macro1()

Dim Col As Long
Dim r As Range

Col = 7 '<=== adjust to suit

Columns("A:A").Copy Cells(1, Col)
Set r = Intersect(ActiveSheet.UsedRange, Columns(Col))
With r
.NumberFormat = "mmmmyyyy"
.AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria1:=Format(Date, "mmmmyyyy")
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
.Clear
End With
End Sub[/VBA]

[expfresocspy]

Hi,

The following code may help. It will filter column A for the Date. The column must be formatted "mmmm" which shows only the Month.

[vba]
Sub DelMonth()
''' This looks at column A for the month only
Dim HowManyVisRows As Long
Dim VisRng As Range
Dim Mymonth As String
Mymonth = Format(Now, "mmmm") '''set to see the mont oly
'apply the filter someway
Dim iCtr As Long
Range("A1").Select
Selection.AutoFilter
With Selection
.AutoFilter Field:=1, Criteria1:=Mymonth '' this set the filtered data for the value
End With
With Worksheets("Sheet2").AutoFilter.Range
'subtract one for the header.
HowManyVisRows _
= .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Cells.Count - 1

If HowManyVisRows >= 2 Then
'avoid the header and come down one row
'and only look at one the first column
Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
VisRng.Offset(1, 0).Select
With ActiveCell
Range(Cells(ActiveCell.Row, 1), Cells(Range("A65536").End(xlUp).Row, 4)).EntireRow.Delete
End With

End If
End With

End Sub
[/vba]

Hi Charles !
Thankyou so very much for the codes (:
I tried it out but there was an error saying : Run-time error '91': Object variable or With block variable not set

My date is displayed in this format : 1/13/2012
So will this code ---> ( Mymonth = Format(Now, "mmmm") '''set to see the mont oly ) be affected by my original date format ?

thanks again for your time !

[expfresocspy]

[vba]Option Explicit
Sub Macro1()

Dim Col As Long
Dim r As Range

Col = 7 '<=== adjust to suit

Columns("A:A").Copy Cells(1, Col)
Set r = Intersect(ActiveSheet.UsedRange, Columns(Col))
With r
.NumberFormat = "mmmmyyyy"
.AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria1:=Format(Date, "mmmmyyyy")
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
.Clear
End With
End Sub[/vba]

Hello mdmackillop !
Thankyou so very much for taking your time to help

I tried out your codes and It works partially in a sense that it deleted the row that is within the date condition but it also deleted the whole entire column according to the code ---> Col = 4

My date is in a column whereas the information of the date is in rows .
I need the entire row to be deleted according to the date instead (: Please assist .

Thankyou thankyou thankyouuuu

[mdmackillop]

The code makes a copy of your data (assumed column A) to a Helper column (7) and uses this for the Delete operation. The Helper column is then deleted. This avoids messing up your own date formatting.

[expfresocspy]

The code makes a copy of your data (assumed column A) to a Helper column (7) and uses this for the Delete operation. The Helper column is then deleted. This avoids messing up your own date formatting.

OUH ! NOW I UNDERSTAND IT CLEARLY !
omg , thankyou so much for showing me the light .
I didn't know there is such thing as a Helper column . So it works like a temporary storage of data for reference (or something along that line right ? :b)

Thankyouuuu ! My problem is solved

[mdmackillop]

. So it works like a temporary storage of data for reference (or something along that line right ? )

Correct. You might also join data form cells eg =A1 & B1 & C1 to create strings which can be filtered for unique records etc., duplicate counts etc., which simplifies (or does away with) the VBA solution