Macro formula

[RebelNev]

Can someone please help me with this one.

If column D contains a zero or negative, I need to delete that entire row, then I need to repeat this action on 11 sheets out of 14 on one workbook, in one action. The 3 sheets that are not to be affected are master data sheets, these are named.

Master
Production reorder
WIP

Thanks in advance.

 

[Benzula]

I'm not the best with macros, but you should be able to use something like this.

The one below deletes the row based on a specific value. So if it contains "Germany" it gets rid of it and so on. You should be able to change it to "" for a blank cell and <0 to get everything below 0.

Again not the best macro writer but should be able to help a little.

Sub DeleteRowsWithSpecificLabel()

Dim MyRange As Range
Dim MyTotal As Long

Set MyRange = Range("G1:G30000")
MyTotal = Range("G1:G30000").Cells.Count
For MyCount = MyTotal To 1 Step -1
If MyRange.Cells(MyCount).Formula = "Australia/New Zealand" Then
MyRange.Cells(MyCount).EntireRow.Delete
End If
Next

Set MyRange = Range("H1:H30000")
MyTotal = Range("H1:H30000").Cells.Count
For MyCount = MyTotal To 1 Step -1
If MyRange.Cells(MyCount).Formula = "Germany" Then
MyRange.Cells(MyCount).EntireRow.Delete
End If
Next


Set MyRange = Range("J1:J30000")
MyTotal = Range("J1:J30000").Cells.Count
For MyCount = MyTotal To 1 Step -1
If MyRange.Cells(MyCount).Formula = "United States" Then
MyRange.Cells(MyCount).EntireRow.Delete
End If
Next


End Sub

 

[Gavin T]

This code should do it:

Sub Merge_Data()

  ' Disable screen updating for more efficiency
    Application.ScreenUpdating = False

  ' Declare Variables
    Dim wSheet As Worksheet

  ' Loop through each sheet in thisworkbook
    For Each wSheet In ThisWorkbook.Worksheets
        With wSheet
          ' Check for correct worksheet
            If Not .Name Like "Master" And .Name Like "Production reorder" And .Name Like "WIP" Then
                With .UsedRange
                    .AutoFilter                                             ' Add autofilter
                    .AutoFilter Field:=4, Criteria1:="<=0", Operator:=xlAnd ' Filter for values <- 0
                    Application.DisplayAlerts = False                       ' Disable excel warnings
                        .SpecialCells(xlCellTypeVisible).Delete             ' Delete visible cells
                    Application.DisplayAlerts = True                        ' Re-Enable excel warnings
                End With
            End If
        End With
    Next wSheet

  ' Re-Enable screen updating
    Application.ScreenUpdating = False
End Sub

Read the comments for an explanation