deleting rows

H

H.

Have a sheet with some 500 rows.

Each row has in column A a date (ranging from januari 1 to whtever end date
but lets say december 31).
In the remaining columns are values I need belonging to the dates
(laboratory results to be more accurate) .
I need a macro that deletes all rows with a date NOT in quarter 1.

Think its easy, but I cannot make one that quick..... :)
Who...?

thx
H.
 
C

CLR

Personally, I would use Data > Filter > AutoFilter > and set a custom filter
on the date column for "greater than and less than" the dates of interest.
That way you could still have the data but yet it would be out of sight.

hth
Vaya con Dios,
Chuck, CABGx3
 
H

H.

Ron,

I do not have the rights to ad an add-in on every computer that is going to
use the workbook in question. So and ad-in is not an option. Thanks anyhow!

H.
 
H

H.

Chuck,

As said, I need a macro. It should leave me a clean number of rows of only
the first quarter only. All other lines HAVE to be gone. I don't want
another user of the sheet to stumble over these other quarters values.

I guess, that I can make a macro around the way you describe. But I have to
autofilter to show only the 3th to 4th quarter. Than delete these rows. And
than reset the autofilter to show the (remaining) first Quarter rows.... but
this option looks a little bit to complex? I thought it might be possible in
an easier way?

H.
 
H

H.

I guess, that I can make a macro around the way you describe. But I have
to autofilter to show only the 3th to 4th quarter. Than delete these rows.
And
Click to expand...

of course meant 2nd to 4th quarter here...

H.
 
R

Ron de Bruin

Try this macro for the range A1:A10000
A1 is a header

Sub Delete_with_Autofilter()
Dim rng As Range

With ActiveSheet
.Range("A1:A10000").AutoFilter Field:=1, Criteria1:=">=" & DateSerial(2006, 4, 1), _
Operator:=xlOr, Criteria2:="<=" & DateSerial(2005, 12, 31)
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End With
.AutoFilterMode = False
End With
End Sub
 
H

H.

Thx Ron,

I will try tomorrow or monday (as soon as I am working on this workbook
again). And of course I let you know if it worked!

H.
 
Top