Excel/vba solution

Advertisement


Question
QUESTION: Hi Jan,

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 E11:BX11

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)

Any suggestions?

Thanks for the help,
Jesse

ANSWER: Hi Jesse,

Is it always row 11 that needs to be moved to the end?

---------- FOLLOW-UP ----------

QUESTION: Thanks for the quick response,

No I only used row 11 because I believe I can apply changes to the row reference based on the code you create.

Basically, I have 2 tables of data. They both occupy the same columns (same width) but one is positioned above the other with only 10 rows or so. The other is below that and extends infinitely. The idea is to add data to the top, small table, and when the values in column BX reach a specified value, an event will trigger that will remove that row of data and place it into the table below at next available row. So I have a temporary table and a permanent table. I have separate events when BX of each row in the (top,smaller,temporary) table reaches various values. I am looking for the code that will take the row when triggered and place it in the bottom table. So I presume that the code will be the same regardless of the row number of the top table...I can just substitute references.

Jesse

Answer
Hi Jesse,

Something like this perhaps?

Sub MoveRow11()
   With ActiveSheet
       'Copy current values and paste below table 2
       .Range("B11:BX11").Copy
       .Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
       'Copy rows up
       .Range(.Range("b12:bx12"), .Range("b12:bx12").End(xlDown)).Copy .Range("B11:BX11")
       'Remove last row which is now duplicated
       .Range("b12").End(xlDown).Resize(, 77).Clear
   End With
End Sub

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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jan Karel Pieterse

Expertise

Excel and Excel/VBA questions

Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 About.com. All rights reserved.