help w/deleting rows if 2 conditions & calling macro from other bo

[Mayte]

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

 

[Shane Devenshire]

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.

 

[fisch4bill]

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 File>Open 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.Count, "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

 

[Mike H]

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.