Solved Relocate specific rows within the same worksheet

October 23, 2014 at 10:32:06
Specs: Windows 7
I need a macro that will find rows that column K shows "X", then cut the rows and insert them further down the same worksheet below a merged row highlighted in orange in Excel 2010.

See More: Relocate specific rows within the same worksheet

Report •


✔ Best Answer
October 27, 2014 at 13:26:49
It would be ActiveSheet, not Active.Sheet (no dot)

ActiveSheet.Cells(srcRw, "F")

The Cells method uses 2 arguments: RowIndex and ColumnIndex

The RowIndex argument has to be a number or an expression that evaluates to a number. The number represents the Row that the cell is in.

The ColumnIndex argument can be a number or a letter or an expression that evaluates to a number or a letter. The number or letter represents the Column that the cell is in.

e.g.

Cells(1, 1) refers to A1
Cells(1, "A") also refers to A1

Cells(3, 6) refers to F3
Cells(3, "F") also refers toF3

You can also use variables:

srcRw = 3
srcCol = 6

Cells(srcRw, srcCol) refers to F3

Or values in worksheet cells:

A1 contains 4
B1 contains 7

Cells(Range("A1"), Range("B1")) refers to G4 as does

Cells(Cells(1, 1), Cells(1, 2))

Or expressions that evaluate to numbers and/or letters:

Cells(4+4, Left("Tom",1)) refers to T8

I could go on all day!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
October 23, 2014 at 12:34:37
While your exact request could probably be fulfilled, it would be easier if the code had something to search for other than "a merged row highlighted in orange".

Is there a specific, unique value that will be in that merged/highlighted row or (better yet) in a specific column within that row that the code could search for? Searching for an exact string is much easier than searching for a specific format. The code doesn't need to know what row it is looking for because it would determine the row number by where it finds the string it is searching for.

If it has to search for a specific format, it would need to search row by row, checking the format of each row to determine if it was merged and/or highlighted in Orange. In fact, even checking for Orange is more difficult these days since Excel now offers so many different shades of Orange.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 23, 2014 at 13:31:05
Sorry about that. The orange row contains the text: "BELOW THIS ORANGE BAR ARE ITEMS THAT HAVE SHIPPED"

message edited by NinjaKitty


Report •

#3
October 24, 2014 at 04:09:53
As I was working on this another question came to mind.

What is the difference between placing this data "below the orange row" and simply continually moving it to the next available row on the sheet? In other words, is this data being moved to an area in between two sets of data or is it just being moved to the bottom of the sheet?

In general, I guess I need a little more detail about how your sheet is laid out and what you are trying to accomplish.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 24, 2014 at 07:58:30
The first 4 rows are taken up with headings.

The next some odd rows are taken up with multiple orders by part number, each order has it's own row.

Each group of orders by part number is separated by a blank row.

Then further down after the last group of active/open orders is the merged row containing the text: "BELOW THIS ORANGE BAR ARE ITEMS THAT HAVE SHIPPED"

The rows below that are where the rows are moved to after an order has been shipped in full. Which is based on the column containing the original order qty being equal to the column containing the actual shipped qty or the column that I've hidden on the sheet with that exact formula to show an "X" when the two cell values have become equal.

Sorry to be so confusing & vague but this workbook is insanely large and so I can't just upload it, also I'd probably get in trouble with my company if I did...

message edited by NinjaKitty


Report •

#5
October 24, 2014 at 18:18:59
Thanks for the explanation, but you haven't really answered my question. Let me be more precise:

Can a row with an X be moved to the next available empty row at the bottom of the existing data? In other words, if you already have 10 rows of "shipped" data below the Orange line, can the next set of "X" rows be moved to the 11th row below the line, the 12th row, etc.

Basically, we would just be extending the list below the orange, row by row, each time a row is cut.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 27, 2014 at 07:51:39
No, it would need to be inserted into the row directly below the orange row.

Or I suppose it could be inserted at the very bottom if after inserting it, the rows below the orange line could then be sorted by the ship date column to bring the most recent rows to the top of the area.

That should give the same result as if it were inserted directly below the orange row to begin with. Right?

message edited by NinjaKitty


Report •

#7
October 27, 2014 at 09:44:58
Inserting "it" directly below the orange row is easy enough, but you've added just a bit more confusion to the issue. Keep in mind that we can't see your worksheet from where we're sitting nor can we read minds. Every little detail matters, especially when VBA is concerned since we need to tell VBA exactly what we want it to do, almost as if we were doing manually, i.e. step by step.

In your original post you used "them", not "it".

"...find rows that column K shows "X", then cut the rows and insert them further down..."

So, are you moving a single row (it) each time or are you running the code to move multiple rows (them) each time?

If you are moving a single row, then pasting it directly below the Orange row is very simple. Cut the row with the "X", Find the words "BELOW THIS ORANGE BAR", Insert the row. Done!

If you are moving multiple rows and they need to stay in some sort of order, then the code needs to know that. In other words, let's say we start at the top of the sheet, search down Column K, find an X and cut/insert it directly below the Orange row. Then we continue down Column K, find another X and cut/insert it directly below the Orange row. Will that data end up in the correct date order? If we continue down Column K and do it again, will that new data end up in the correct date order?

If the X's above the Orange row are in some sort of random order, then something will need to be sorted but I won't know how to handle that until I know more about the row(s?) with the X('s?) and how the dates in the upper section influence their positioning after each cut/insert.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
October 27, 2014 at 10:03:21
Ok, this should help...

	A	B	C	D	E	F	G	H
3				Due			Ship	Ship
4	Order #	PO	Item #	Date	Qty		Qty	Date
5							
6	5870	23475	11C	10/17	468 	X	468	10/16
7	6046	25772	11C	10/31	396 		278	10/23
8	6153	26868	11C	11/7	468 			
9	6184	27608	11C	11/14	396 			
10	6454	30411	11C	12/12	396 			
11	6455	30563	11C	1/9	396 			
12							
13	5871	23478	12C	10/17	468 	X	468	10/16
14	6047	25773	12C	10/31	396 		380	10/23
15	6154	26872	12C	11/7	468 			
16	6185	27613	12C	11/14	396 			
17	6456	30564	12C	1/9	396 			
18							
19	6183	27606	13B	11/14	72 			
20	6339	29539	13B	12/5	36 			
21	6457	30785	13B	12/19	36 			
22	6458	30786	13B	1/9	36 			
23		31283	13B	12/19	36 			
24							
25	6182	27607	14B	11/14	72 		1	10/8
26	6459	30787	14B	12/19	36 			
27	6460	30788	14B	1/9	36 			
28		31284	14B	12/19	36 			
29							
30	6048	25822	15A	10/24	544 	X	544	10/22
31	6155	26870	15A	11/7	612 		37	10/22
32	6237	28151	15A	11/21	544 			
33	BELOW THIS ORANGE BAR ARE ITEMS THAT HAVE SHIPPED					X		
34	5991	24557	23A	10/24	3,000 	X	3000	10/23
35	6048	25822	15A	10/24	544 	X	544	10/22
36	5878	23755	16A	10/24	612 	X	612	10/22
37	6050	25770	94A	10/24	36	X	36	10/22
38	6327	29187	94A	10/24	36	X	36	10/22
39	6051	25771	95A	10/24	36	X	36	10/22
40	6110	26421	96	10/24	1,500 	X	1500	10/22
41	6045	25769	40	10/17	2,000 	X	2000	10/17
42	5876	23477	15A	10/17	612 	X	612	10/15
43	5877	23754	15A	10/17	612 	X	612	10/15
44	5922	24118	23A	10/17	3,000 	X	3000	10/16

message edited by NinjaKitty


Report •

#9
October 27, 2014 at 10:09:54
As new orders come in they are entered onto new rows that are inserted below the rows containing prior orders of the same part number.

Once an order has shipped in full the row is then cut and inserted below the orange row in no particular order other than the orders that shipped most recently would then be directly below the orange bar, however the part numbers themselves might not be listed in correct order.

For example; Rows 6, 13 & 30 would be each cut and inserted above line 34 and below line 33 due to column F showing an "X" based upon columns E & G being equal.


Report •

#10
October 27, 2014 at 11:05:12
You original post said:

"I need a macro that will find rows that column K shows "X"..."

Your example and your text in Response #9 use Column F.

Which is it?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
October 27, 2014 at 11:52:06
Ok, you caught me. I was trying to keep my examples simpler than my actual spreadsheet by hiding columns that didn't contain pertinent information...

This spreadsheet actually contains columns A through Z with order specific data in each row starting at row 6 through row 581.

Columns AA through AZ contain calculation formulas at row 5 based on multiple points of data found throughout A1:D1 & A6:Z581.

Cells A1:D1 contain formulas to auto-calculate this Friday's date and the following cells then contain the subsequent Friday dates after that starting at A1 and figuring through to D1.

All other cells contain headings & other key type information.

By the end of the year this workbook will contain 52 different worksheets, with each new worksheet being created by copying the current worksheet every Monday & changing the title of the duplicated tab to Monday's date.

Like I said, I was trying to simplify it a bit...

Please just refer to column F as if it was the K that I earlier referred to. I should be able to figure out which column's to adjust the formula to in the end.


Report •

#12
October 27, 2014 at 12:00:13
This code will move any Rows with an X in Column F to the row below the row containing "BELOW THIS ORANGE BAR ARE ITEMS THAT HAVE SHIPPED".

It actually copies all the "X" rows first and then goes back and deletes them.

When deleting rows with VBA you need to do it in reverse order (from bottom to top) or the loop counter will get screwed up. However, since you want the rows that are moved to retain the same date order (I think), they have to be moved in the order that they are found. Therefore the code works in a forward direction to copy the rows and then in reverse order to delete them. In the end, I think you end up with what you want.

I strongly suggest that you try this code in a backup copy of your workbook since macros cannot be undone.

Sub MoveShippedItems()
Dim oRw As Range
Dim dstRw, srcRw, lastRw As Integer

'Disable ScreenUpdating to eliminate flicker as code is running
  Application.ScreenUpdating = False
  
'Find current location of Orange row
   With Sheets("B").Cells
    Set oRw = .Find("BELOW THIS ORANGE", lookat:=xlPart)
   End With

'Set Destination row to be one row below Orange row
   dstRw = oRw.Row + 1
'Copy/Insert each row with an X to the Destination Row
    For srcRw = 6 To oRw.Row - 1
      If Sheets("B").Cells(srcRw, "F") = "X" Then
        Sheets("B").Cells(srcRw, "F").EntireRow.Copy
        Sheets("B").Cells(dstRw, "A").Insert
      End If
    Next

'Delete each upper section row that contains an X
'Must be done in reverse order.
   lastRw = oRw.Row - 1
     For srcRw = lastRw To 6 Step -1
       If Sheets("B").Cells(srcRw, "F") = "X" Then
         Sheets("B").Cells(srcRw, "F").EntireRow.Delete
       End If
     Next

End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
October 27, 2014 at 12:10:28
What am I doing wrong?

First I tried running it exactly as you had the code written, but nothing happened.

So then I tried the following and still nothing seems to be working...

I've tried deleting this part to see if I might not be giving it enough time to run through the whole sheet by watching for the screen to flicker...

'Disable ScreenUpdating to eliminate flicker as code is running
  Application.ScreenUpdating = False

No flickers anywhere...

I've tried changing all occurrences of

Sheets("B").Cells
to be
ActiveSheet.Cells
I've also tried changing it to this
Sheets("10-27-14").Cells

I've tried changing this

(srcRw, "F").
to this
(srcRw, "L").
or this
(srcRw, L).

I've tried running it directly from the code as well as off of an ActiveX button.

I've tried doing all of the above changes separately & together. However I get the same result each time I try to run it... nothing...

What am I doing wrong??

message edited by NinjaKitty


Report •

#14
October 27, 2014 at 13:26:49
✔ Best Answer
It would be ActiveSheet, not Active.Sheet (no dot)

ActiveSheet.Cells(srcRw, "F")

The Cells method uses 2 arguments: RowIndex and ColumnIndex

The RowIndex argument has to be a number or an expression that evaluates to a number. The number represents the Row that the cell is in.

The ColumnIndex argument can be a number or a letter or an expression that evaluates to a number or a letter. The number or letter represents the Column that the cell is in.

e.g.

Cells(1, 1) refers to A1
Cells(1, "A") also refers to A1

Cells(3, 6) refers to F3
Cells(3, "F") also refers toF3

You can also use variables:

srcRw = 3
srcCol = 6

Cells(srcRw, srcCol) refers to F3

Or values in worksheet cells:

A1 contains 4
B1 contains 7

Cells(Range("A1"), Range("B1")) refers to G4 as does

Cells(Cells(1, 1), Cells(1, 2))

Or expressions that evaluate to numbers and/or letters:

Cells(4+4, Left("Tom",1)) refers to T8

I could go on all day!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •


Ask Question