That was it, once I converted to date format, this worked like a charm.
Thanks so much Rick!
"Rick Rothstein" wrote:
> The code was tested before I posted it and it worked fine. The only reason,
> off the top of my head, that I can think of why it didn't work for you is
> that your "dates" are not really Excel dates, rather, I think your dates
> might be text representation of dates instead.
>
> --
> Rick (MVP - Excel)
>
>
> "SITCFanTN" <(E-Mail Removed)> wrote in message
> news:8E992DC9-5A9A-4A87-84CA-(E-Mail Removed)...
> > HI Rick,
> >
> > This deleted all my records, it didn't just delete the records that were
> > not
> > dated for today. I only changed my sheet name...I changed your text of
> > "sheet 7" to "All Records"
> >
> > Help, thank you.
> >
> > "Rick Rothstein" wrote:
> >
> >> Give the following macro a try; change my example setup in the three
> >> Const
> >> statements to match your actual layout. Note that I interpreted your
> >> reference to "current date" to be today's date; if that was wrong, then
> >> change the <>Date condition in the first If statement to the date value
> >> you
> >> actually want to use.
> >>
> >> Sub RemoveNotCurrentRecords()
> >> Dim X As Long
> >> Dim LastRow As Long
> >> Dim OriginalCalculationMode As Long
> >> Dim RowsToDelete As Range
> >>
> >> Const DataStartRow As Long = 1
> >> Const UnionColumn As String = "E"
> >> Const SheetName As String = "Sheet7"
> >>
> >> On Error GoTo Whoops
> >> OriginalCalculationMode = Application.Calculation
> >> Application.Calculation = xlCalculationManual
> >> Application.ScreenUpdating = False
> >>
> >> With Worksheets(SheetName)
> >> LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
> >> For X = LastRow To DataStartRow Step -1
> >> If .Cells(X, UnionColumn).Value <> Date Then
> >> If RowsToDelete Is Nothing Then
> >> Set RowsToDelete = .Cells(X, UnionColumn)
> >> Else
> >> Set RowsToDelete = Union(RowsToDelete, .Cells(X, UnionColumn))
> >> End If
> >> If RowsToDelete.Areas.Count > 100 Then
> >> RowsToDelete.EntireRow.Delete xlShiftUp
> >> Set RowsToDelete = Nothing
> >> End If
> >> End If
> >> Next
> >> End With
> >> If Not RowsToDelete Is Nothing Then
> >> RowsToDelete.EntireRow.Delete xlShiftUp
> >> End If
> >> Whoops:
> >> Application.Calculation = OriginalCalculationMode
> >> Application.ScreenUpdating = True
> >> End Sub
> >>
> >> --
> >> Rick (MVP - Excel)
> >>
> >>
> >> "SITCFanTN" <(E-Mail Removed)> wrote in message
> >> news:8542DAE9-E688-466D-9B8D-(E-Mail Removed)...
> >> > The date in my table is in mm/dd/yy format and located in column E. I
> >> > need
> >> > to delete all rows not the current date quickly. I want to build a
> >> > macro
> >> > in
> >> > VB to delete all records not the current date. Any help you have is
> >> > greatly
> >> > appreciated, thank you.
> >>
> >> .
> >>
>
> .
>
|
Similar Threads
