deleting rows
 

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.

deleting rows
 

Hi H.

Depend when your quarter start
EasyFilter have a option to do this
http://www.rondebruin.nl/easyfilter.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"H." wrote in message ...
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.

deleting rows
 

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." wrote:

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.

deleting rows
 

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.


"Ron de Bruin" schreef in bericht
...
Hi H.

Depend when your quarter start
EasyFilter have a option to do this
http://www.rondebruin.nl/easyfilter.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"H." wrote in message
...
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.

deleting rows
 

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.


"CLR" schreef in bericht
...
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." wrote:

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.

deleting rows
 

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

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

H.

deleting rows
 

Is Jan 1 the first day of quarter 1?



--
Regards Ron de Bruin
http://www.rondebruin.nl


"H." wrote in message ...
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.


"Ron de Bruin" schreef in bericht ...
Hi H.

Depend when your quarter start
EasyFilter have a option to do this
http://www.rondebruin.nl/easyfilter.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"H." wrote in message ...
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.

deleting rows
 

yes...
and the last day is 31 of march

"Ron de Bruin" schreef in bericht
...
Is Jan 1 the first day of quarter 1?

deleting rows
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"H." wrote in message ...
yes...
and the last day is 31 of march

"Ron de Bruin" schreef in bericht ...
Is Jan 1 the first day of quarter 1?

deleting rows
 

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.



"Ron de Bruin" schreef in bericht
...
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"H." wrote in message
...
yes...
and the last day is 31 of march

"Ron de Bruin" schreef in bericht
...
Is Jan 1 the first day of quarter 1?