Year row removal

[smurray444]

Dear all,

I have two datasets which I need to merge into one. One dataset (sheet 1 in Excel 2002) has data for stations consistantly for years 1958 to 1990 (column A). The second dataset (sheet 2, column D), whilst having data for the same number of stations, does not have these consistant years - for example, some stations may only have data for say, 1958-1964, 1962-1986, 1982-1990 etc etc.

Is there a way through which rows of data in the first dataset that have years which do not correspond to those of the second dataset, can be deleted? e.g. If, for a particular station, data in the first dataset runs from 1958-1990 (as is always the case), yet data in the second dataset runs from only 1965-1980, then rows in the second dataset containing years 1958-1964 and 1981-1990 need to be removed.

Could you please design a way of achieving this?

Thanks for your help and time,
Steve Murray

 

[acw]

Steve

This may get you started.

Sub bbb()
 For i = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
  If WorksheetFunction.CountIf(Sheets("sheet2").Range("a:a"), Sheets("sheet1").Cells(i, 1)) = 0 Then
   Sheets("sheet1").Cells(i, "A").EntireRow.Delete
  End If
 Next i
End Sub

Tony