How to Delete a Row based on a date

I have a spreadsheet with about 225 sheets or so containing data. The [b]date[/b] is in [b]column J[/b]. I

am needing to [b]delete[/b] all the [b]Rows[/b] that have a date [b]greater than or equal[/b] to

[b]06/06/08[/b] and loop through all of the sheets.

I recorded a macro to find the date 6/6/2008, then highlight the row, then select the next 450 rows and delete them and it worked ok, but I don't know how to get it to loop through the sheets to the end.

I was wondering if somebody could help me out with a macro/vba.

Any help would be GREATLY appreciated!!!
Thank you!

Comments

  • fairly easy to walk thru all the worksheets, see code or uploaded file

    [code]
    Sub x()
    Const cColumnJ = 10 'COLUMN=J
    Dim myRow As Integer
    Dim ws As Worksheet
    Dim vCellValue As Variant

    Dim myRowStr As String
    Dim myDate As Date
    myDate = CDate("6/6/2008")

    'enumerate worksheets collection
    For Each ws In Worksheets

    'select worksheet
    Sheets(ws.Name).Select

    'traverse cells, from last used cell to first one
    For myRow = ws.UsedRange.Rows.Count To 1 Step -1

    'get cell value
    vCellValue = ws.Cells(myRow, cColumnJ)
    'is value a date?
    If IsDate(vCellValue) Then
    'compare date, delete row
    If vCellValue >= myDate Then ws.Rows(myRow).Delete
    End If

    Next myRow

    Next ws

    MsgBox "Done!", vbInformation
    End Sub
    [/code]
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion