Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VBA Delete Duplicates and Blank Rows

  1. #1
    New Member
    Join Date
    Jun 2017
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Delete Duplicates and Blank Rows

    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.

    Code:
    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.

  2. #2
    New Member
    Join Date
    Jun 2017
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Delete Duplicates and Blank Rows

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

    Code:
    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

  3. #3
    Board Regular
    Join Date
    Mar 2013
    Posts
    619
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Delete Duplicates and Blank Rows

    @ 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

  4. #4
    New Member
    Join Date
    Jun 2017
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Delete Duplicates and Blank Rows

    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

  5. #5
    Board Regular
    Join Date
    Mar 2013
    Posts
    619
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Delete Duplicates and Blank Rows

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •