Easy Excel Formula - Need to move certain area (4x4) rows to the left.  Applescript is okay too.

Posted on 2011-05-06
Medium Priority
Last Modified: 2012-05-11
Hi Excel Experts,

I have attached a document as a example.  

I have these invoices that are exported from my software.  Depending on how much items we order, will depend how many rows are on the excel document.

I need to move (the highlighted part) to the left.  I need to do this, because I plan on using another formula which is going to reference the whole column. (and I don't want the subtotal part messing up my other formula). If someone can help me move those columns to the left in a formula or apple script that would solve this issue.  Thanks!

P.S.  The formula must work for both sheets on the workbook.  Since some orders will have one product; while others will have 15 products etc.... Example2.xlsx
Question by:activematx
  • 2
LVL 59

Accepted Solution

Chris Bottomley earned 2000 total points
ID: 35712633
A bit of VBA to move on the active sheet is as below.

Alt + F11 to see the VBE editor, insert module and paste the code there.

Use F5 to select a macro to run and select moveblock

Sub moveblock()
Dim rng1 As Range
Dim rng2 As Range

    Set rng1 = ActiveSheet.Columns(8).Find(What:="subtotal", After:=ActiveSheet.Columns(8).Cells(1), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    Set rng2 = ActiveSheet.Columns(8).Find(What:="total", After:=rng1, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    With ActiveSheet.Range(rng1, rng2.Resize(, 2))
        .Cut Destination:=.Offset(0, -3)
    End With
End Sub

Open in new window


Author Closing Comment

ID: 35713125
This guy is awesome.  Very talented.
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35713154
Very kind, but i'm just one of many here.


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question