Help with macro to delete rows based on values

Hi,

I have a following code and I need help fixing it.

First of all I have 5 sheets and I'm wondering if it can be adjusted so that the first part of formula goes thru 5 sheets at once rather them me changing the sheet a repeating the formula again.

in the 2nd part of the formula i'm wondering how it can be adjusted so that It can delete other valuer rather then just value "50"
for example values like" 50, 30, 25".

I also need a 3rd part which would be formula to go thru column c and delete rows if they have various values.

I would appreciate if someone can help me out with this and most important is to keep the formula simple so I can understand what's going on,
or maybe explain how it was done.

Thank you very much.

Please Login or Register  to view this content.

PHP Code: 

Sub GPE_COM()
 Dim X As Long, last_Row As Long
 Dim dRng As Range
    
 Sheets("ARP").Select   'SHEET ARP'
 last_Row = Range("A80536").End(xlUp).Row
 For X = last_Row To 1 Step -1
    If Range("D" & X).Value < CDate("01/01/2011") Or Range("A" & X).Value = 50 Then '...'
        If dRng Is Nothing Then
            Set dRng = Range("D" & X).EntireRow
        Else
            Set dRng = Union(dRng, Range("D" & X).EntireRow)
        End If
    End If
 Next X
 
 If Not dRng Is Nothing Then dRng.Delete
  
End Sub 


Originally Posted by Sa DQ

PHP Code: 

Sub GPE_COM()
 Dim X As Long, last_Row As Long
 Dim dRng As Range
    
 Sheets("ARP").Select   'SHEET ARP'
 last_Row = Range("A80536").End(xlUp).Row
 For X = last_Row To 1 Step -1
    If Range("D" & X).Value < CDate("01/01/2011") Or Range("A" & X).Value = 50 Then '...'
        If dRng Is Nothing Then
            Set dRng = Range("D" & X).EntireRow
        Else
            Set dRng = Union(dRng, Range("D" & X).EntireRow)
        End If
    End If
 Next X
 
 If Not dRng Is Nothing Then dRng.Delete
  
End Sub 


Since this is for 5 sheets I didn't know how to adjust it better so I copied the formula 5 times and tried running it but I'm getting a error message "method union of object global failed" and it gets stuck at this line "**Set*dRng*=*Range("D"*&*X).EntireRow"

Is there anyway to fix that?

O yes and the code is super super fast
********
************thanks

Originally Posted by Sa DQ

PHP Code: 

Sub GPE_COM()
 Dim X As Long, last_Row As Long
 Dim dRng As Range, Sh As WorkSheet       '**'
 
 For Each Sh In ThisWorkBook.WorkSheets   '**'
    If InStr("ARP@ARC&ARU&ARY@ARH.", Sh.Name) Then    '**'
'      Sheets("ARP").Select   SHEET ARP'
       Sh.Select    '**'
       last_Row = Range("A80536").End(xlUp).Row
       For X = last_Row To 1 Step -1
          If Range("D" & X).Value < CDate("01/01/2011") Or Range("A" & X).Value = 50 Then '...'
              If dRng Is Nothing Then
                  Set dRng = Range("D" & X).EntireRow
              Else
                  Set dRng = Union(dRng, Range("D" & X).EntireRow)
              End If
          End If
       Next X 
       If Not dRng Is Nothing Then dRng.Delete
       Set dRng = Nothing    '**'
    End If
 Next Sh      '**'
End Sub 


Note: Where "ARC", "ARU" , "ARY" and "ARH" are Sheet Names

Thank you again, but that's not exactly what I had in mind because with solving this problem new one is created.
On the old example I was able to change column reference for each sheet.
now code assumes that each sheet is exact same and that data is on every sheet in the same column, and that's not the case.

for example if my ARH sheet has data that i'm looking for in columns E and N, i'm not able to adjust that with this code.

Any suggestions for going around this problem?

Originally Posted by Trebor76

Please list what columns (that contain the date and values) for each sheet name that the macro is to be run on.

Sheets in the the woorkbook (ARP, APP, OSO, OPOS, POC)

1. Dates
All sheets column D except POC which is column I.

2. Values (50 or 57)
All sheets column A
After finishing steps 1 and 2 I'm looking into going to steps 3 and 4


3. Values (75FLG or 94VIL or values that start with 2)
All column B except POC sheet which is column D.

4. On sheet APP look at column J for amounts with (-) negative.


The bottom line is to find all this values on all the sheets and delete the rows that contain those values.

Thank you

Originally Posted by Trebor76

Try this on a copy of your workbook in case the results are not as expected:

Please Login or Register  to view this content.

Note, any row in the range that is completly blank will also be deleted. This can be stopped if need be.

Regards,

Robert

Thank. you Robert