Excel Code for deleting

Microsoft Excel 2007
October 22, 2009 at 10:53:18
Specs: Windows Vista
I have an Excel workbook with 4 worksheets in it. The code is set up on the 1st worksheet. When a quantity is entred in a cell under the Quantity column then the information in 4 specific cells are populated into the other 3 spreadsheets. However, if I put the quantity value in the wrong cell and go to delete it the information is not deleted ont he other worksheets as well and I want them to be. Here is the code, hope this makes sense

Private Sub Worksheet_Change(ByVal Target As Range)
'Check if a value is entered in Quantity column (J) and that it is > 0
If Target.Column = 10 Then
If Target > 0 Then
'Determine next empty row in Quote sheet
nxtQuoRow = 21
If Sheets("Quote").Cells(nxtQuoRow, "C") <> "" Then
nxtQuoRow = nxtQuoRow + 1
GoTo chkQuoRow
End If
'Copy data from Pricing Tool sheet to Quote sheet
Sheets("Quote").Range("C" & nxtQuoRow) = Cells(Target.Row, "J")
Sheets("Quote").Range("D" & nxtQuoRow) = Cells(Target.Row, "A")
Sheets("Quote").Range("E" & nxtQuoRow) = Cells(Target.Row, "B")
Sheets("Quote").Range("K" & nxtQuoRow) = Cells(Target.Row, "H")
End If
End If

'Check if a value is entered in Quantity column (J) and that it is > 0
If Target.Column = 10 Then
If Target > 0 Then
'Determine next empty row in Purchase Order sheet
nxtPurRow = 21
If Sheets("Purchase Order").Cells(nxtPurRow, "C") <> "" Then
nxtPurRow = nxtPurRow + 1
GoTo chkPurRow
End If
'Copy data from Pricing Tool sheet to Purchase Order sheet
Sheets("Purchase Order").Range("C" & nxtPurRow) = Cells(Target.Row, "J")
Sheets("Purchase Order").Range("D" & nxtQuoRow) = Cells(Target.Row, "A")
Sheets("Purchase Order").Range("E" & nxtQuoRow) = Cells(Target.Row, "B")
Sheets("Purchase Order").Range("K" & nxtQuoRow) = Cells(Target.Row, "E")
End If
End If

'Check if a value is entered in Quantity column (J) and that it is > 0
If Target.Column = 10 Then
If Target > 0 Then
'Determine next empty row in Invoice sheet
nxtInvRow = 21
If Sheets("Invoice").Cells(nxtInvRow, "C") <> "" Then
nxtInvRow = nxtInvRow + 1
GoTo chkInvRow
End If
'Copy data from Pricing Tool sheet to Invoice sheet
Sheets("Invoice").Range("C" & nxtInvRow) = Cells(Target.Row, "J")
Sheets("Invoice").Range("D" & nxtInvRow) = Cells(Target.Row, "A")
Sheets("Invoice").Range("E" & nxtInvRow) = Cells(Target.Row, "B")
Sheets("Invoice").Range("K" & nxtInvRow) = Cells(Target.Row, "H")
End If
End If
End Sub

See More: Excel Code for deleting

Report •

October 22, 2009 at 12:01:10
May I suggest that you use the "pre" tags found above the message box when posting code? When you use the pre tags, the code will hold it's indented format and make it easier to read. See the code I posted below to see what I mean.

That said, I assume that if you delete an entry on Sheet1, you want the last sets of data that were written to the other sheets deleted also. That is fairly easy to do.

However, if you want specific values deleted based on a specific value deleted from Sheet1 that's going to be a bit tougher.

Following the same "logic" used in the code you posted, if you added the code below before the End Sub line, it would delete the last row of values that were copied to the Invoice sheet.

Replicate the code for the other sheets to delete the last row on those sheets also.

Please note that I am following the same logic as in the code you posted, without saying that it is the most efficient way to write code. For example, your code checks the target column and value 3 times when it really only needs to be checked once. It also repeats a lot of other steps that could be condensed, but we'll save that for another time and another place.

Anyway, in similar fashion, this snippet should take care of the Invoice sheet. If you delete *any* value in Column 10 (J), the last set of data copied to the Invoice sheet will be deleted.

 If Target.Column = 10 Then
  If Target = "" Then
   NxtInvRow = 21
    If Sheets("Invoice").Cells(NxtInvRow, "C") <> "" Then
     NxtInvRow = NxtInvRow + 1
     GoTo chkLastInvRow
    End If
      LastInvRow = NxtInvRow - 1
      Sheets("Invoice").Range("C" & LastInvRow).ClearContents
      Sheets("Invoice").Range("D" & LastInvRow).ClearContents
      Sheets("Invoice").Range("E" & LastInvRow).ClearContents
      Sheets("Invoice").Range("K" & LastInvRow).ClearContents
  End If
 End If

Report •

October 22, 2009 at 12:23:38
Thank you for the response. This did work for what you said it would do. However, what I want to do is the more difficult scenario you posted. I want specific values deleted based on a specific value deleted from Sheet1 that's. If it is too hard I will not worry about it.
Also, I am interested in condensing this code I am very new at this.
I have an example workbook if it is easier for you to see what I am trying to do. Thanks!

Report •

October 22, 2009 at 12:58:17
In order to delete values from the other sheets based on a specific deleted value in Sheet1, VBA would need to know what the value was before it was deleted so it can look it up on the other sheets. Once it's deleted, it's gone and VBA can't do what you want based on that change.

You could, however, use some code behind a button which would you allow you to select the value you want to delete, click the button and then have VBA take care of the deletions on the other sheets, including deleting the value selected.

The problem with that method comes with dealing with multiple instances of that value on the other sheets.

For example, if you want to delete a specific "10" from Column J on Sheet1 but your other sheets have 3 rows that have "10" in whatever Column you tell it to look at, there would have to be more criteria involved so it would know which "10" (and it's associated data) to delete. That's where it gets complicated.

A fairly simple...

With Worksheets(2).Range("a1:a500")
    Set c = .Find(10)
    If Not c Is Nothing Then
        'Do some deleting here
    End If
End With

would only find the first 10 in the range but that might not be the 10 you were looking for. You would then need to check something else to narrow it down to the correct 10.

We would need some more specifics before we could offer any suggestions.

Report •

Related Solutions

October 22, 2009 at 15:30:45
Thanks for the info. It does not sound like there is a perfect solution to my problem, so I am just not going to worry about it anymore.
Thanks for all your help you have solved my problems!

Report •

Ask Question