software

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.

Hi H.

Depend when your quarter start
EasyFilter have a option to do this
www.rondebruin.nl/easyfilter.htm
--
Regards Ron de Bruin
www.rondebruin.nlquot;H.quot; gt; wrote in message ...
gt; Have a sheet with some 500 rows.
gt;
gt; Each row has in column A a date (ranging from januari 1 to whtever end date but lets say december 31).
gt; In the remaining columns are values I need belonging to the dates (laboratory results to be more accurate) .
gt; I need a macro that deletes all rows with a date NOT in quarter 1.
gt;
gt; Think its easy, but I cannot make one that quick..... :-)
gt; Who...?
gt;
gt; thx
gt; H.
gt;
gt;
gt;
Personally, I would use Data gt; Filter gt; AutoFilter gt; and set a custom filter
on the date column for quot;greater than and less thanquot; 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
quot;H.quot; wrote:

gt; Have a sheet with some 500 rows.
gt;
gt; Each row has in column A a date (ranging from januari 1 to whtever end date
gt; but lets say december 31).
gt; In the remaining columns are values I need belonging to the dates
gt; (laboratory results to be more accurate) .
gt; I need a macro that deletes all rows with a date NOT in quarter 1.
gt;
gt; Think its easy, but I cannot make one that quick..... :-)
gt; Who...?
gt;
gt; thx
gt; H.
gt;
gt;
gt;
gt;

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.quot;Ron de Bruinquot; gt; schreef in bericht
...
gt; Hi H.
gt;
gt; Depend when your quarter start
gt; EasyFilter have a option to do this
gt; www.rondebruin.nl/easyfilter.htm
gt;
gt;
gt;
gt; --
gt; Regards Ron de Bruin
gt; www.rondebruin.nl
gt;
gt;
gt; quot;H.quot; gt; wrote in message
gt; ...
gt;gt; Have a sheet with some 500 rows.
gt;gt;
gt;gt; Each row has in column A a date (ranging from januari 1 to whtever end
gt;gt; date but lets say december 31).
gt;gt; In the remaining columns are values I need belonging to the dates
gt;gt; (laboratory results to be more accurate) .
gt;gt; I need a macro that deletes all rows with a date NOT in quarter 1.
gt;gt;
gt;gt; Think its easy, but I cannot make one that quick..... :-)
gt;gt; Who...?
gt;gt;
gt;gt; thx
gt;gt; H.
gt;gt;
gt;gt;
gt;gt;
gt;
gt;
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.quot;CLRquot; gt; schreef in bericht
...
gt; Personally, I would use Data gt; Filter gt; AutoFilter gt; and set a custom
gt; filter
gt; on the date column for quot;greater than and less thanquot; the dates of interest.
gt; That way you could still have the data but yet it would be out of sight.
gt;
gt; hth
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;H.quot; wrote:
gt;
gt;gt; Have a sheet with some 500 rows.
gt;gt;
gt;gt; Each row has in column A a date (ranging from januari 1 to whtever end
gt;gt; date
gt;gt; but lets say december 31).
gt;gt; In the remaining columns are values I need belonging to the dates
gt;gt; (laboratory results to be more accurate) .
gt;gt; I need a macro that deletes all rows with a date NOT in quarter 1.
gt;gt;
gt;gt; Think its easy, but I cannot make one that quick..... :-)
gt;gt; Who...?
gt;gt;
gt;gt; thx
gt;gt; H.
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt; I guess, that I can make a macro around the way you describe. But I have
gt; to autofilter to show only the 3th to 4th quarter. Than delete these rows.
gt; And

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

H.
Is Jan 1 the first day of quarter 1?
--
Regards Ron de Bruin
www.rondebruin.nlquot;H.quot; gt; wrote in message ...
gt; Ron,
gt;
gt; 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
gt; option. Thanks anyhow!
gt;
gt; H.
gt;
gt;
gt; quot;Ron de Bruinquot; gt; schreef in bericht ...
gt;gt; Hi H.
gt;gt;
gt;gt; Depend when your quarter start
gt;gt; EasyFilter have a option to do this
gt;gt; www.rondebruin.nl/easyfilter.htm
gt;gt;
gt;gt;
gt;gt;
gt;gt; --
gt;gt; Regards Ron de Bruin
gt;gt; www.rondebruin.nl
gt;gt;
gt;gt;
gt;gt; quot;H.quot; gt; wrote in message ...
gt;gt;gt; Have a sheet with some 500 rows.
gt;gt;gt;
gt;gt;gt; Each row has in column A a date (ranging from januari 1 to whtever end date but lets say december 31).
gt;gt;gt; In the remaining columns are values I need belonging to the dates (laboratory results to be more accurate) .
gt;gt;gt; I need a macro that deletes all rows with a date NOT in quarter 1.
gt;gt;gt;
gt;gt;gt; Think its easy, but I cannot make one that quick..... :-)
gt;gt;gt; Who...?
gt;gt;gt;
gt;gt;gt; thx
gt;gt;gt; H.
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
yes...
and the last day is 31 of march

quot;Ron de Bruinquot; gt; schreef in bericht
...
gt; Is Jan 1 the first day of quarter 1?
Try this macro for the range A1:A10000
A1 is a header

Sub Delete_with_Autofilter()
Dim rng As Range

With ActiveSheet
.Range(quot;A1:A10000quot;).AutoFilter Field:=1, Criteria1:=quot;gt;=quot; amp; DateSerial(2006, 4, 1), _
Operator:=xlOr, Criteria2:=quot;lt;=quot; amp; 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--
Regards Ron de Bruin
www.rondebruin.nlquot;H.quot; gt; wrote in message ...
gt; yes...
gt; and the last day is 31 of march
gt;
gt; quot;Ron de Bruinquot; gt; schreef in bericht ...
gt;gt; Is Jan 1 the first day of quarter 1?
gt;
gt;
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.
quot;Ron de Bruinquot; gt; schreef in bericht
...
gt; Try this macro for the range A1:A10000
gt; A1 is a header
gt;
gt; Sub Delete_with_Autofilter()
gt; Dim rng As Range
gt;
gt; With ActiveSheet
gt; .Range(quot;A1:A10000quot;).AutoFilter Field:=1, Criteria1:=quot;gt;=quot; amp;
gt; DateSerial(2006, 4, 1), _
gt; Operator:=xlOr, Criteria2:=quot;lt;=quot; amp; DateSerial(2005, 12, 31)
gt; With ActiveSheet.AutoFilter.Range
gt; On Error Resume Next
gt; Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
gt; .SpecialCells(xlCellTypeVisible)
gt; On Error GoTo 0
gt; If Not rng Is Nothing Then rng.EntireRow.Delete
gt;
gt; End With
gt; .AutoFilterMode = False
gt; End With
gt; End Sub
gt;
gt;
gt; --
gt; Regards Ron de Bruin
gt; www.rondebruin.nl
gt;
gt;
gt; quot;H.quot; gt; wrote in message
gt; ...
gt;gt; yes...
gt;gt; and the last day is 31 of march
gt;gt;
gt;gt; quot;Ron de Bruinquot; gt; schreef in bericht
gt;gt; ...
gt;gt;gt; Is Jan 1 the first day of quarter 1?
gt;gt;
gt;gt;
gt;
gt;