Clear selection of cells matching cells in a different range.

[bujubenji]

Hi all,

I have a workbook that records whether goods have been sent to quality assurance.

I have a simple macro which copies cells B:E (starting at row 44) to the next clear row in columns H:K when a shape (acting as a button) is pressed. There is a button on each row of the array.

There have been a few occasions of people accidentally clicking the shape and copying data over. I want to code it so that when the button is pressed again, it deletes the corresponding data from that row. e.g. Data from B45:E45 was accidentally copied over to the next clear row (say H50:K50 for argument's sake), a second click of the button would find the relevant entry and clear the cell contents (and ideally, would shift all subsequent data up so that there weren't any random gaps). I've had a dig around and can't find a solution. I was attempting to use Range.Find but didn't know if I could reference the cells as opposed to hardcoded values. Can you point me in the right direction please?

Hugely grateful for any help you can give.




Sub btnR44_click()

With ActiveSheet

Range("B44:E44").Copy
Range("B44:E44").Interior.Color = RGB(136, 212, 138)
.Range("H" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Application.CutCopyMode = False

.Shapes("btnR44").Visible = False
.Shapes("R44clk").Visible = True

Application.Speech.Speak "sent to Q A", speakasync:=True


End With

End Sub
_________

Sub btnR44_unclick()

With ActiveSheet

.Shapes("btnR44").Visible = True
.Shapes("R44clk").Visible = False
Range("B44:E44").Interior.Color = xlNone

End With

End Sub

 

[WaywardWes]

Spitballing here a bit, but could you find the next range of open cells, then store that range in a variable, then paste using the variable? Then if you need to "unclick" you can refer to that hard coded range in the variable.