Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
resetting last cell | Excel Discussion (Misc queries) | |||
stop users from deleting rows | Excel Discussion (Misc queries) | |||
Deleting rows in a macro in Excel | Excel Discussion (Misc queries) | |||
Question about deleting rows from sorted linked worksheets | Excel Worksheet Functions | |||
Deleting rows containing common data | Excel Discussion (Misc queries) |