I'm trying to create a code that is triggered when a value in a column reaches a particular value. I don't need help triggering the event. I am stuck on part the code that runs when the event is triggered:
I need it to do the following.
1) clear the contents of cells in the row from E:BX
2) move the contents of the rows below that row up (now that there is a gap in the table)
3) add the contents of the row that was cleared to the bottom row of the table beginning at E38:BX38 (use endup column e)
From E8:BQ27 is my first table. Table 2 is E32:BQ(extends to bottom of workbook)
Say my event for row 8 is triggered. I'd like contents of row 8 to be cleared and added to the next available row in table 2(endup column E). Then, since there is data below the row that was just cleared from table 1, that data needs to shift up to fill in the empty row.
Thanks for the help,
You said E:BX, but your example was to BQ. This code worked for me using BQ (and as I understand the requirement).
You can alter it to do what you want. I have assumed that Table1 will always end above row 30. Further, even though you say table 2 goes to the end of the worksheet, I assume the last row in the worksheet will be blank. I never clear row 8 because I paste row 9 on top of it - so that seems to achieve the same result. Further assumes no merged cells in the ranges being worked with.
Dim r As Range, r1 As Range, r2 As Range
Dim rw1 As Long, rw As Long
rw = 8
Set r = Range(Cells(rw, "E"), Cells(rw, "BQ"))
Set r1 = Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
rw1 = Cells(30, "E").End(xlUp).Row
Set r2 = Range(Cells(rw + 1, "E"), Cells(rw1, "BQ"))
Range(Cells(rw1, "E"), Cells(rw1, "BQ")).ClearContents