VBA Delete Duplicates and Blank Rows

[pahickham]

Hello All,

Info:
I've created a spreadsheet that filter data from and inventory list to 2 other sheets one titled "low" and the other titled "out of service" when the "actual qty" falls bellow the "req qty" in the inventory list it's filtered to the "low" sheet. When the "req qty" turns to zero it will be cut and pasted into the "Out of service" sheet.

Problem Statement:

If the "actual qty" equals the "req qty" it gets removed form the low sheet, but when this happens it leaves an empty row of cells. I've placed this code in to get rid of the duplicates, but it doesn't remove the empty cells. I'm uncertain of how to combine the two codes.

Private Sub Worksheet_Change(ByVal Target As Range)

    With ActiveSheet
        Set Rng = Range("A1", Range("H1").End(xlDown))
        Rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    End With
End Sub

Here's the link to my excel sheet
https://app.box.com/s/wfxmfiew0pp9m4ehr3opzeepzgdidn1o

Thanks for any help in advance.

 

[pahickham]

Also here's the code im using to copy and paste/ cut and paste. Any suggestions are appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)Dim i, LastRow


LastRow = Sheets("Inventory").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Out of Service").Range("A2:I500").ClearContents
Sheets("Low").Range("A2:I500").ClearContents
For i = 2 To LastRow


If Sheets("Inventory").Cells(i, "E").Value = "0" Then
Sheets("Inventory").Cells(i, "H").EntireRow.Cut Destination:=Sheets("Out of Service").Range("A" & Rows.Count).End(xlUp).Offset(1)
Sheets("Inventory").Range("H:H").SpecialCells(xlBlanks).EntireRow.Delete
End If


If Sheets("Inventory").Cells(i, "D").Value < Cells(i, "E") Then
Sheets("Inventory").Cells(i, "H").EntireRow.Copy Destination:=Sheets("Low").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If


Next i
End Sub

 

[NoSparks]

@ pahickham

You should have edited your original post rather than replying to it.

You'll notice in the menu bar near the top of the page there's Zero Reply Posts,
this is where regulars, looking to help someone, find post that aren't getting any help.
You managed to removed yourself from that list just 2 minutes after your original post.

Anyhow, your downloaded workbook has 3 sheets with all the same headers.
Have you thought of having just the one sheet with additional columns for Low and Out of Service ?
A couple simple If formulas could populate these columns and a little real filtering could show what (I think) the three sheets are supposed to.

A link to a sample example
https://app.box.com/s/0htrrzv0b16uvpjj1vjc1w3ozwpzgllf

 

[pahickham]

Haha I didn't realize that! As far as your idea goes that's not a bad idea and I like what you did. On my original excel document I actually have 2 more sheets, received and outgoing, that are synced to the inventory list so that when i input in them they either add or subtract from the given SAP. do you think the macro will still be able to work with that, because i still kind of need that. Also i do kind of like having the out of service list separate from all of this.

Original Idea:

https://app.box.com/s/bya0thspar454ut5cvtxzgsa6dc9zywi

 

[NoSparks]

Received and Outgoing included.
box link
https://app.box.com/s/797bdafch0rtoi6u2a98u5dq894bxj2e
Re: out of service, I personally wouldn't remove them from the overall data unless you are permanently deleting them.
You can always copy the filtered results if needed.