Filter

[Eileen]

I need to sort out an report (hundred of rows)on daily
basis to show the items that have backlog (see below
examples). I appreciate the help.

FROM:

Item Backlog Open PO Qty
NP100 -400 300
500
1500
NP100 300 2000
4000
2000
WCG200 -200 3000
2400
RESULT:
Item Backlog Open PO Qty
NP100 -400 300
500
1500
WCG200 -200 3000
2400

 

[Greg Wilson]

This assumes that the sheet containing the data is
named "Data" and the sheet to receive the backlog-only
data is named "Backlog". Also assumed is that there are
headers in the first row of each of the above sheets.
Therefore, the data starts in Cell A2 of Sheet "Data".
Transfer of backlog-only data will start in Cell A2 of
Sheet "Backlog".

Ensure that you have a backup of your data before testing.
Not rigorously tested - that's your job!!! Hope my
interpretation was correct:

Sub ShowBackLog()
Dim Rng As Range
Dim WS As Worksheet
Dim i As Long, ii As Long, Rw As Long
Dim TransferData As Boolean

Application.ScreenUpdating = False
Set WS = Sheets("Data")
With WS
Rw = .Cells(Rows.Count, 3).End(xlUp).Row
Set Rng = Range(.Cells(2, 1), .Cells(Rw, 3))
End With
Set WS = Sheets("Backlog")
ii = 1
TransferData = False
For i = 1 To Rng.Rows.Count
If Rng(i, 2) <> "" Then TransferData = (Rng(i, 2) < 0)
If TransferData Then
ii = ii + 1
WS.Cells(ii, 1) = Rng(i, 1)
WS.Cells(ii, 2) = Rng(i, 2)
WS.Cells(ii, 3) = Rng(i, 3)
End If
Next
WS.Activate
Application.ScreenUpdating = True
End Sub

Regards,
Greg

 

[Debra Dalgleish]

You could add another column to the table, and calculate whether to show
the row. For example, in cell F2, enter the following formula:

=IF(A2="",F1,IF(B2<0,"X",""))

Copy the formula down to the last row of data
Select a cell in the table, and choose Data>Filter>AutoFilter
From the dropdown arrow in the new column, select "X"