Quote:
Originally Posted by DeFautT
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.
***