Hi Pierre,
Assumed that your dates are in column A of sheet1.
Working on a COPY of your workbook , try:
Sub Tester()
'//////////////////////////////////////////////////
' Note: Amend Const NumOfstocks required number!
' Note: Amend the WS (sheet name) to your worksheet!
'//////////////////////////////////////////////////
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim WS As Worksheet
Const NumOfstocks = 10 '<<< CHANGE!
Set WS = ActiveWorkbook.Sheets("Sheet1")
'<< Change to your sheet name!
With WS
Set rng1 = .Range("A1").CurrentRegion
Set rng2 = rng1.Offset(1).Resize(rng1.Rows.Count - 1)
rng1.Resize(, 1).Insert Shift:=xlToRight
rng2.Columns(1).AutoFit
Set rng3 = rng2.Resize(, 1).Offset(, -1)
rng3.FormulaR1C1 = _
"=COUNTIF(C[1]:C[1],RC[1])<" & NumOfstocks
rng3(1).Offset(-1).Value = "Condition"
.Range("A1").AutoFilter
rng1.AutoFilter Field:=1, Criteria1:="TRUE"
On Error Resume Next ' in case no rows to delete
rng3.SpecialCells(xlCellTypeVisible). _
EntireRow.Delete
On Error GoTo 0
rng1.AutoFilter
.Columns(1).Delete
End With
End Sub
---
Regards,
Norman
"Pierre " wrote in message
...
Many Tx, Norman, but my problem is a little bit different... Let me try
to be more explcit :
I have 10 stocks.
Each stocks has 2 columns : dates and prices
What I want is to keep the date that are in the 10 stocks and remove
the ones that are not common to all stocks.
Let's say I have a price of $34 on the 05/23/03 for stock One but this
day is not in stock Two, then remove the 05/23/03 from stock One
The goal is not to remove all the holidays but to generate a consistent
matrix ie a matrix with a price for every stocks at every dates
I hope It is clearer... (the problem is that when you spend a lot of
time on a problem, a lot of assumptions seem obvious... but they are
not !!):( ;) :)
---
Message posted from http://www.ExcelForum.com/