Hi,

This macro part does whay you want. It starts from assuming the RAW workbook
is closed. It open is and deletes all rows on sheet 1 that do not contain
'ATTENDANCE' in Col G

I don't understand what you mean though by row J being blank. Under what
circulstances do we delete if row J is blank?

Is it regardless of what is in column G?

Sub stantial()
Dim wb As Workbook
Path = "C:\" ' change to suit
Set wb = Workbooks.Open(Path & "raw.xls", True, True)
Set sht = wb.Sheets("Sheet1")

Dim myrange, MyRange1 As Range
lastrow = sht.Cells(Rows.Count, "G").End(xlUp).Row

Set myrange = sht.Range("G1:G" & lastrow)
For Each c In myrange
If UCase(c.Value) < "ATTENDANCE" 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
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"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