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

Hi,

If you don't mind moving from one workbook to the other:

Sub DeleteRows()
Application.ScreenUpdating = False
Windows("Raw.xlsx").Activate
Sheets("Sheet1").Select
Columns("L:L").Insert
Range("L1:L" & [A1048000].End(xlUp).Row).Select
Selection = "=IF(OR(RC[-5]<""Attendance"",RC[-2]=""""),TRUE,1)"
Selection.SpecialCells(xlCellTypeFormulas, 4).EntireRow.Delete
Columns("L:L").Delete
Windows("Book1").Activate
End Sub

Note in this case the starting workbook is Book1, you would change that in
your code.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"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

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

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