Transfer rows with varying criteria
 

Hi all,

This is the second time i have to write this message (blooming interne
exploder closed on me!) Anyway, again - thanks for all the recent hel
and hope someone can sort out this little macro for me.

Below is a macro to transfer rows where a date is matched:-

Sub ARCHIVEDAILYLIST()

Dim SourceRange As Range, TargetRange As Range
Dim SearchRange As Range, LastWrittenCell As Range
Dim i, n As Integer, k As Integer, ItemToSearchFor
'--------------------------------------
' User definitions
n = 15 ' number of columns to append
Set SourceRange = [FF1]
Set TargetRange = Range("ARCHIVE!A1")
'--------------------------------------
Set SearchRange = Range(SourceRange, SourceRange.End(xlDown))
If IsEmpty(TargetRange) Then
Set LastWrittenCell = TargetRange
k = 0
Else
k = 1
If IsEmpty(TargetRange.Offset(1, 0)) Then
Set LastWrittenCell = TargetRange
Else
Set LastWrittenCell = TargetRange.End(xlDown)
End If
End If

ItemToSearchFor = [FG1] 'A cell containing the current date
If ItemToSearchFor = "" Then
Exit Sub
End If

For Each i In SearchRange
If i.Value = ItemToSearchFor Then
Range(i, i.Offset(0, n - 1)).Copy LastWrittenCell.Offset(k + j, 0)
j = j + 1
End If
Next
Sheets("ARCHIVE").Select

End Sub

This works great for the purpose but i need to now do somethin
slightly different. INstead of transferring rows where a date is th
criteria, i need to transfer rows where in one column a non null valu
appears:

DATE PRODUCT QUANTITY

A B 1
A C 2
A D
A E 3

In the above example all rows except for product "D" are transferred
Any help in adapting the above would be much appreciated. Thanks agai
for all the help.

Chri

--
chris10
-----------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...fo&userid=2516
View this thread: http://www.excelforum.com/showthread.php?threadid=47600