First off you'll have to have both workbooks open in the same instance of
Excel (don't click on the file in an Explorer window, use the FileOpen menu
within Excel for both files) - if you don't you'll get an 'Out of Range'
error. Next you'll need to modify the reference to the range by adding the
"Workbooks" level like so:

lastrow = Workbooks("raw.xls").Sheets("Sheet1").Cells(Rows.C ount, "G").End _
(xlUp).Row
Set myrange = Workbooks("raw.xls").Sheets("Sheet1").Range("G1:G" & lastrow)

That lets Excel know which workbook to use when assigning the values to your
variables.
Next substitute your string for "TEST" like so:

If NOT c.Value = "Attendance" Then

and patch in another For-Next loop where you test for "" as c.Value in
Column "J".

HTH
Bill

"Mayte" wrote:

hi, i found the code below and it works when i'm in the same workbook but how
do i modify it so that i call from book 1 and it works on book 2. i also need
to add a second condition .... any ideas??

what i want to do is delete all rows that DON'T have "Attendance" in column
G and aferwards delete all rows that are "blank" in column J

the macro will be called from the master.xls BUT will work on another file
called raw.xls


Sub stantial()
Dim myrange, MyRange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Sheets("Sheet1").Range("A1:A" & lastrow)
For Each c In myrange
If UCase(c.Value) = "TEST" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If

End Sub