Solved

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

Posted on 2011-05-06
Medium Priority
390 Views
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
0
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

Chris
``````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, _
MatchCase:=False)
Set rng2 = ActiveSheet.Columns(8).Find(What:="total", After:=rng1, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False)
With ActiveSheet.Range(rng1, rng2.Resize(, 2))
.Cut Destination:=.Offset(0, -3)
End With

End Sub
``````
0

LVL 9

Author Closing Comment

ID: 35713125
This guy is awesome.  Very talented.
0

LVL 59

Expert Comment

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

Regards
Chris
0

## Featured Post

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â€¦
###### Suggested Courses
Course of the Month8 days, 19 hours left to enroll