VBA Delete Excel Rows Based on Certain Date

Let say I have a bunch of formatted data in my Excel sheet, and in Column A I have a dates. Then I wanna filter all the data base on certain date, for example 1 Jan 2009, and delete all others data before that date.

The algorithm is like this: I’ll create a loop from the first row until the last row with data and check if the date is before Jan 1st, 2009 or not, if it is, simply delete that row.

Sub DeleteFilteredRows()
    Dim i As Long

    For i = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
        Debug.Print Cells(i, "A").Value
        If CDate(Cells(i, "A")) < CDate("1/jan/2009") Then
            Cells(i, "A").EntireRow.Delete
        End If
    Next i

End Sub

Of course I’ll make an adjustment to the code above to meet my current condition.

FIN.

Related Entries

  • Excel VBA Looping Tutorial

    Do while … loop, do until … loop, do … loop while, do … loop until, and while … wend are the format of looping that we can use in Excel VBA. The main reason why we need to perform looping is to simplified a repeating tasks. Rather then manually calling a block of code over...

  • Number of Cells/Rows/Columns With Formula

    A few weeks ago I received this question from someone called LS: Hi I want to write a macro that counts the following - Number of cells with formula - Number of Rows with formula - Number of Columns with formula Please let me know if there is a way of doing it in Excel VBA To find out the number of...

  • Handy VBA To Calculate Person Age

    This handy VBA procedure is just to show you how we can calculate and display someone Age. To do this, of course we need to know their date of birth first, then simply using datediff and mod vba functions we can calculate how old is he/she.Sub PersonAge() Dim iYears As Integer, iMonths...

  • How To Find the Last Row That Contain Data in Excel?

    UPDATE June 13, 2008: Another alternative way to find the last row with data :Function LastRow() As LongDim ix As Long ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count LastRow = ixEnd FunctionI did a simple test, and the function will return the correct last row position...

  • Schedules to Refresh Data Automatically using VBA Macro

    I once asked by Joshuacht about how to make a macro running every 15 minutes. Let say because every 15 minutes we have to refresh or reload the data in the Workbook. Below is a sample of how we can do this in Microsoft Excel *just an outline*. The logic is simple, when we open the...

Valuable Resources

If you found this page useful, please consider bookmark it using social media or add a link to this page.

  • Print
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • connotea
  • Diigo
  • email
  • Fark
  • Identi.ca
  • Live
  • MisterWong
  • MySpace
  • Netvibes
  • NewsVine
  • PDF
  • Ping.fm
  • Propeller
  • Reddit
  • Simpy
  • StumbleUpon
  • Technorati
  • Twitter
  • Wikio
  • Yahoo! Bookmarks

Incoming search engine terms: excel macro delete row, excel vba delete row, vba macro date, VBA delete row, delete rows based on date excel vba, test for a date in excel vba, macro for dates in excel, macro to check the date in excel, macro delete rows, macro delete row date, how to remove rows with certain value, how to remove a row vba, how to delete column F in excel, find and delete column with certain, filter date vba

«
»