VBA that will delete rows that have a matching value and automatically save as a pdf file after group/process

[ubergreen]

I am trying to create a vba code that will allow me to click on a button and delete all rows that have the same matching number and have the remaining rows shift up after the rows are deleted. The reference cell will always be A2, which will be used to check if it matches any other numbers that are also in column A. This is all on Sheet1.

The purpose of all of this is because I have a more formal order form, on a separate sheet, Sheet2, that will be referencing these values from Sheet1 that will then automatically pull up the associated info, such as pricing, etc, for each item in the order. IE, using the picture example below, order number 101 would have 3 items, S, M, and XL.

I would like for only Sheet2 to be saved as a pdf automatically after each group of numbers have been processed. The only thing I am kind of worried about this step is that I am not sure if the file would reliably be able to process all of the information required on Sheet2 before it is saved as a PDF. Maybe a 1 second delay before saving?


Original

After 1st click

After 2nd click

After 3rd click

After 4th click

 

[Burrgogi]

I am trying to create a vba code that will allow me to click on a button and delete all rows that have the same matching number and have the remaining rows shift up after the rows are deleted.

So if this is what you want, then why did you delete cell A5 containing 102? I don't see any other cells containing that value (102).

 

[ubergreen]

So if this is what you want, then why did you delete cell A5 containing 102? I don't see any other cells containing that value (102).

I am assuming that you're referring to 102 as it appears in the second image/example? I guess my thought process was that 102 would match itself, so it would then be deleted.

If that was an issue, I guess you could say delete row 2 regardless and any rows that have a matching value in column A.

 

[ubergreen]

This is about as close as I have been able to find something that is close to what I need for deleting rows with the matching values

Sub DeleteMatchingRows()

Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = lastRow To 1 Step -1
    If Cells(i, "A").Value = "" Then
        Rows(i).Delete
    End If
Next i

End Sub

I found this code online that was made to delete rows if the cells in column A were blank.

I can see that the code in the middle, If Cells(i, "A").Value = "" Then, is the part of the code that gives the blank cell parameter. I can see that I change the value within the "", then it will delete the values that matches accordingly.

The issue that I am encountering is I don't know how to simply assign a reference cell instead of a fixed value. I thought typing If Cells(i, "A").Value = A2 Then would work, but that doesn't seem like it is doing anything.

 

[Cubist]

Try this on a copy.

Sub DeleteRowsMatchingValue()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim deleteRange As Range
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet name
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set deleteRange = Nothing
    For i = 2 To lastRow
        If ws.Cells(i, "A").Value = ws.Cells(2, "A").Value Then
            If deleteRange Is Nothing Then
                Set deleteRange = ws.Rows(i)
            Else
                Set deleteRange = Union(deleteRange, ws.Rows(i))
            End If
        End If
    Next i
   
    If Not deleteRange Is Nothing Then deleteRange.Delete
    Set ws = Nothing
    Set deleteRange = Nothing
End Sub