Hello,

Can anyone help with a macro to delete a row of data if colomn C (date) is before 1/1/2010. I want to anaylize the data from this year olny. I can do an auto format to get rid of the info, but the info gets updated every month and all previous data gets dumped back into the file. I need to stream line it so that if anyone else opens the file they only see this years info.

Thanks in advance for the help.

Terry

Hello,

the most easy way to delete certain rows can be done by sorting
and after that remove unnecessary rows. But if you want use macro,
the following code will work. ( Try first with copy of your file )





Sub DeleteCertainRows()

' BEFORE YOU RUN THIS MACRO SELECT AN CONTINUOUS
' RANGE CONTAININIG JUST THAT PART OF ONE COLUMN
' WHERE THE DATES ARE.
' SELECT RANGE WITH MOUSE FROM TOP TO BOTTOM FOR EXSAMPLE
' DONT SELECT ENTIRE COLUMN, BECAUSE CHECKING EVERY ROW AT WORKSHEET
' WILL TAKE SOME TIME



Dim ViRi As Long ' last cell in selection
Dim ActCol As Long ' active column in selection
Dim Acel As Long ' active cell when macro starts
Dim AkRi As Long ' next row to be Checked out
Dim SearchVal As Date ' earlier date than this will be removed

On Error Resume Next

' THIS WILL TAKE SCREENUPDATING OFF - MACRO RUNS FASTER
' THEN YOU WILL BE ASKED FOR LATEST DATE WHICH WILL REMAIN AT TABLE

Application.ScreenUpdating = False
SearchVal = DateValue(InputBox("Give the date "))

' THIS DETERMINES THE SMALLEST ROWNUMBER IN SELECTION
' BECAUSE IT IS NOT NECESSARILY THE ROWNUMBER OF
' ACTIVECELL ( DEPENDS OF HOW YOU SELECTED THE CELLS
' AT THE FIRST PLASE

Acel = ActiveCell.Row
For Each Item In Selection
If Item.Row < Acel Then
Acel = Item.Row
End If
Next Item


' THIS PART WILL SET STARTING VALUES FOR THE REST OF VARIABLES

ActCol = ActiveCell.Column
ViRi = Selection.Rows.Count
AkRi = 0

' THEN MACRO WILL SELECT LAST CELL FROM CURRENT SELECTION
' PLEASE, SELECT ONLY ONE COLUMN, THAT WHICH CONTAIN DATES YOU
' WANT TO EXAMINE, BEFORE YOU RUN THIS MACRO

Cells(ViRi + Acel - 1, ActCol).Select

' THIS LOOP DO THE MOST OF THE WORK OF THIS MACRO
' IT START FROM BIGGEST ROWNUMBER AND CHECKS ROW BY ROW
' THE VALUE OF CURRENT CELL IN THE COLUMN THAT WAS
' ACTIVE WHEN MACRO WAS STARTED

For CheckOut = 1 To ViRi
If (ActiveCell.Offset(AkRi, 0).Value < SearchVal) Then
ActiveCell.Offset(AkRi, 0).EntireRow.Delete
End If
AkRi = AkRi + 1 * -1
Next CheckOut

End Sub


Some combinations of ( and ) - marks will be seen as smileys at this
forum and I dont know how should I put the code here so that they
will not counted as smileys.

If someone knows that , feel free to advice me with that knowledge, thanks.

So you may have to check the code concerning ) marks. It worked with
my worksheet.

***