Move rows with specific text

Question

Hi All,

Is there any macro to move the rows with text "Period" in Column A, to after the row with text "Invoice No." in Excel?

Delivery Type	Pick up
Subject Co ID	187119
Invoice No.	1234566
Secondry Address	AIXTR-K
Member 1	LM-RKT01ZC
Member 2	R1005BG
Member 3	TE-068
Range	100-150
Period	3 months
Break Up	Not Applicable
Description	Attacjed

Answer 1

Unfortunately I couldn't reply sooner, so I'm probably too late.

Nevertheless, hereby I give you my code.

When your data ('Delivery type') start in A2 of "Sheet1", you can use:

Sub macro1() 

Dim i As Integer, p As Integer 

With Sheets("Sheet1") 

p = Application.Match("Period", .Range("A2:A500"), 0) + 1 

i = Application.Match("Invoice No.", .Range("A2:A500"), 0) + 1 

.Rows(p).Cut 

.Rows(i + 1).EntireRow.Insert 

End With 

End Sub

Answer 2

My Bad!

Try adding the following line after the lastRow line to check if the cells are being referenced correctly:

Debug.Print lastRow

Then run the macro and see what the value of lastRow is.

If the value of lastRow is correct, try changing the condition in the If statement to this:

If Cells(lastRow, "A"). Value = "" Then

This will check the value of the last row in column A to see if it's blank. If it is, the row will be deleted.

Answer 3

Hi AnnaThomasH

Thank you for the help, this overwrite the row "Secondry Address" instead of insert as a new row between "Invoice No." and "Secondry Address".

Also, the blank row needs to be removed once the movement done.

Thanks & regards

Answer 4

Can "Period" in column A appear in more than 1 cell?

Answer 5

Hi Nagul

I'm AnnaThomas and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

You can use a macro to move rows with the text "Period" in Column A to after the row with the text "Invoice No." in Excel. Here's a simple VBA macro that you can use:

Sub MoveRows() Dim source As Range Dim target As Range

Set source = Range("A:A"). Find("Period") Set target = Range("A:A"). Find("Invoice No.")

source. EntireRow.Cut target. EntireRow.Offset(1) End Sub

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.