Excel/VBA Solution


Hi Tom,
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)

For example:
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.

Any suggestions?

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.

Sub abcd()
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)
r1.PasteSpecial xlValues
r1.PasteSpecial xlFormats
rw1 = Cells(30, "E").End(xlUp).Row
Set r2 = Range(Cells(rw + 1, "E"), Cells(rw1, "BQ"))
r.PasteSpecial xlValues

Range(Cells(rw1, "E"), Cells(rw1, "BQ")).ClearContents
End Sub

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 About.com. All rights reserved.