Help updating this code to sort current region by Column A then Column G?

[Coyotex3]

Hello, I have a code that finds a specific word and delete 3 rows above it(therefore removing blank cells between two similar ranges.) I would like to be able to select the current region and resort. Here is what I have come up with so far. This removes my columns for me but I can't figure out the rest.

Sub Delete_Rows()
    Range("A" & Rows.Count).End(xlUp).Select
    Const WordToLook = "NYC"
    Dim RowWord As Long
    RowWord = Columns(4).Find(WordToLook, LookAt:=xlWhole).Row
    Rows(RowWord - 3 & ":" & RowWord - 1).Delete
End Sub

 

[Coyotex3]

Should have used XL2BB to begin with. This is a similar format but the ranges will vary in length, but I would like to go from this:

Order Template.xlsx
	A	B	C	D	E	F	G	H	I
1	Order Recipient	Primary Address	Secondary Address	City	City, State, Zip Code	Primary Order	Secondary Order	Third Order
2	Jane Doe 1	123 Lets Do It	Getting There	NJ	123 Lets Do it, Getting There, NJ	45612	45123	45699
3	Jane Doe 2	124 Lets Do It	Getting There	NJ	124 Lets Do it, Getting There, NJ	45613	45123	45699
4	Jane Doe 3	125 Lets Do It	Getting There	NJ	125 Lets Do it, Getting There, NJ	45614	45123	45699
5
6
7
8	John Doe 1	123 Make it Happen	150 We Did It	New York	Manhattan, NY, 10001	12545	54568	75854
9	Blake Doe	124 Make it Happen	151 We Did It	New York	Manhattan, NY, 10002	12546	54569	75854
10	John Doe 3	125 Make it Happen	152 We Did It	New York	Manhattan, NY, 10003	12547	54570	75854
11	John Doe 4	126 Make it Happen	153 We Did It	New York	Manhattan, NY, 10004	12548	54571	75854
12	John Doe 5	127 Make it Happen	154 We Did It	New York	Manhattan, NY, 10005	12549	54572	75854
13	John Doe 6	128 Make it Happen	155 We Did It	New York	Manhattan, NY, 10006	12550	54573	75854
14
15
16
17	Ann Doe	129 Make it Happen		NYC	Manhattan, NY, 10007	12551	54574	75854
18	John Doe 8	130 Make it Happen		NYC	Manhattan, NY, 10008	12552	54575	75854
19	John Doe 9	131 Make it Happen		NYC	Manhattan, NY, 10009	12553	54576	75854
20
Sheet30

[XR][XH][/XH][XH=w:80]A[/XH][XH=w:105]B[/XH][XH=w:94]C[/XH][XH=w:51]D[/XH][XH=w:154]E[/XH][XH=w:71]F[/XH][XH=w:83]G[/XH][XH=w:58]H[/XH][XH=w:48]I[/XH][/XR][XR][XH]8[/XH][XD=h:l|ch:15.75|fz:12pt]Ann Doe[/XD][XD=h:l|fz:12pt]129 Make it Happen[/XD][XD][/XD][XD=h:l|fz:12pt]NYC[/XD][XD=h:l|fz:12pt]Manhattan, NY, 10007[/XD][XD=fz:12pt]12551[/XD][XD=fz:12pt]54574[/XD][XD=fz:12pt]75854[/XD][XD][/XD][/XR][XR][XH]9[/XH][XD=h:l|ch:15.75|fz:12pt]Blake Doe[/XD][XD=h:l|fz:12pt]124 Make it Happen[/XD][XD=h:l|fz:12pt]151 We Did It[/XD][XD=h:l|fz:12pt]New York[/XD][XD=h:l|fz:12pt]Manhattan, NY, 10002[/XD][XD=fz:12pt]12546[/XD][XD=fz:12pt]54569[/XD][XD=fz:12pt]75854[/XD][XD][/XD][/XR][XR][XH]10[/XH][XD=h:l|ch:15.75|fz:12pt]John Doe 1[/XD][XD=h:l|fz:12pt]123 Make it Happen[/XD][XD=h:l|fz:12pt]150 We Did It[/XD][XD=h:l|fz:12pt]New York[/XD][XD=h:l|fz:12pt]Manhattan, NY, 10001[/XD][XD=fz:12pt]12545[/XD][XD=fz:12pt]54568[/XD][XD=fz:12pt]75854[/XD][XD][/XD][/XR][XR][XH]11[/XH][XD=h:l|ch:15.75|fz:12pt]John Doe 3[/XD][XD=h:l|fz:12pt]125 Make it Happen[/XD][XD=h:l|fz:12pt]152 We Did It[/XD][XD=h:l|fz:12pt]New York[/XD][XD=h:l|fz:12pt]Manhattan, NY, 10003[/XD][XD=fz:12pt]12547[/XD][XD=fz:12pt]54570[/XD][XD=fz:12pt]75854[/XD][XD][/XD][/XR][XR][XH]12[/XH][XD=h:l|ch:15.75|fz:12pt]John Doe 4[/XD][XD=h:l|fz:12pt]126 Make it Happen[/XD][XD=h:l|fz:12pt]153 We Did It[/XD][XD=h:l|fz:12pt]New York[/XD][XD=h:l|fz:12pt]Manhattan, NY, 10004[/XD][XD=fz:12pt]12548[/XD][XD=fz:12pt]54571[/XD][XD=fz:12pt]75854[/XD][XD][/XD][/XR][XR][XH]13[/XH][XD=h:l|ch:15.75|fz:12pt]John Doe 5[/XD][XD=h:l|fz:12pt]127 Make it Happen[/XD][XD=h:l|fz:12pt]154 We Did It[/XD][XD=h:l|fz:12pt]New York[/XD][XD=h:l|fz:12pt]Manhattan, NY, 10005[/XD][XD=fz:12pt]12549[/XD][XD=fz:12pt]54572[/XD][XD=fz:12pt]75854[/XD][XD][/XD][/XR][XR][XH]14[/XH][XD=h:l|ch:15.75|fz:12pt]John Doe 6[/XD][XD=h:l|fz:12pt]128 Make it Happen[/XD][XD=h:l|fz:12pt]155 We Did It[/XD][XD=h:l|fz:12pt]New York[/XD][XD=h:l|fz:12pt]Manhattan, NY, 10006[/XD][XD=fz:12pt]12550[/XD][XD=fz:12pt]54573[/XD][XD=fz:12pt]75854[/XD][XD][/XD][/XR][XR][XH]15[/XH][XD=h:l|ch:15.75|fz:12pt]John Doe 8[/XD][XD=h:l|fz:12pt]130 Make it Happen[/XD][XD][/XD][XD=h:l|fz:12pt]NYC[/XD][XD=h:l|fz:12pt]Manhattan, NY, 10008[/XD][XD=fz:12pt]12552[/XD][XD=fz:12pt]54575[/XD][XD=fz:12pt]75854[/XD][XD][/XD][/XR][XR][XH]16[/XH][XD=h:l|ch:15.75|fz:12pt]John Doe 9[/XD][XD=h:l|fz:12pt]131 Make it Happen[/XD][XD][/XD][XD=h:l|fz:12pt]NYC[/XD][XD=h:l|fz:12pt]Manhattan, NY, 10009[/XD][XD=fz:12pt]12553[/XD][XD=fz:12pt]54576[/XD][XD=fz:12pt]75854[/XD][XD][/XD][/XR][XR][XH]17[/XH][XD=ch:15.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]18[/XH][XD=ch:15][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR]

I have no idea what you are talking about.
Please explain clearly & succinctly what you are trying to do.

Will do my best! Sorry.

 

[Coyotex3]

Should have used XL2BB to begin with. This is a similar format but the ranges will vary in length, but I would like to go from this:

Order Template.xlsx
	A	B	C	D	E	F	G	H	I
1	Order Recipient	Primary Address	Secondary Address	City	City, State, Zip Code	Primary Order	Secondary Order	Third Order
2	Jane Doe 1	123 Lets Do It	Getting There	NJ	123 Lets Do it, Getting There, NJ	45612	45123	45699
3	Jane Doe 2	124 Lets Do It	Getting There	NJ	124 Lets Do it, Getting There, NJ	45613	45123	45699
4	Jane Doe 3	125 Lets Do It	Getting There	NJ	125 Lets Do it, Getting There, NJ	45614	45123	45699
5
6
7
8	John Doe 1	123 Make it Happen	150 We Did It	New York	Manhattan, NY, 10001	12545	54568	75854
9	Blake Doe	124 Make it Happen	151 We Did It	New York	Manhattan, NY, 10002	12546	54569	75854
10	John Doe 3	125 Make it Happen	152 We Did It	New York	Manhattan, NY, 10003	12547	54570	75854
11	John Doe 4	126 Make it Happen	153 We Did It	New York	Manhattan, NY, 10004	12548	54571	75854
12	John Doe 5	127 Make it Happen	154 We Did It	New York	Manhattan, NY, 10005	12549	54572	75854
13	John Doe 6	128 Make it Happen	155 We Did It	New York	Manhattan, NY, 10006	12550	54573	75854
14
15
16
17	Ann Doe	129 Make it Happen		NYC	Manhattan, NY, 10007	12551	54574	75854
18	John Doe 8	130 Make it Happen		NYC	Manhattan, NY, 10008	12552	54575	75854
19	John Doe 9	131 Make it Happen		NYC	Manhattan, NY, 10009	12553	54576	75854
20
Sheet30

To this

Order Template.xlsx
	A	B	C	D	E	F	G	H	I
8	Ann Doe	129 Make it Happen		NYC	Manhattan, NY, 10007	12551	54574	75854
9	Blake Doe	124 Make it Happen	151 We Did It	New York	Manhattan, NY, 10002	12546	54569	75854
10	John Doe 1	123 Make it Happen	150 We Did It	New York	Manhattan, NY, 10001	12545	54568	75854
11	John Doe 3	125 Make it Happen	152 We Did It	New York	Manhattan, NY, 10003	12547	54570	75854
12	John Doe 4	126 Make it Happen	153 We Did It	New York	Manhattan, NY, 10004	12548	54571	75854
13	John Doe 5	127 Make it Happen	154 We Did It	New York	Manhattan, NY, 10005	12549	54572	75854
14	John Doe 6	128 Make it Happen	155 We Did It	New York	Manhattan, NY, 10006	12550	54573	75854
15	John Doe 8	130 Make it Happen		NYC	Manhattan, NY, 10008	12552	54575	75854
16	John Doe 9	131 Make it Happen		NYC	Manhattan, NY, 10009	12553	54576	75854
17
18
Sheet30

@Fluff I would like for this macro to look at the current region find where column D is NYC remove the three rows above it so now we don’t blank spaces between New York and NYC. Then I need for the macro to select the range where we just removed the blank spaces from in this case “A8:A16” and to sort ONLY those rows by Column A and then G.

I will be back home in about an hour and can post an XL2BB showing The whole range.

 

[Fluff]

Will the 1st row to sort always be row 8, or will it always be from the 2nd block of text?

 

[Coyotex3]

@Fluff here is the whole picture.

Order Template.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Order Recipient	Primary Address	Secondary Address	City	City, State, Zip Code	Primary Order	Secondary Order	Third Order
2	Jane Doe 1	123 Lets Do It	Getting There	NJ	123 Lets Do it, Getting There, NJ	45612	45123	45699
3	Jane Doe 2	124 Lets Do It	Getting There	NJ	124 Lets Do it, Getting There, NJ	45613	45123	45699
4	Jane Doe 3	125 Lets Do It	Getting There	NJ	125 Lets Do it, Getting There, NJ	45614	45123	45699
5
6
7
8	Ann Doe	129 Make it Happen		NYC	Manhattan, NY, 10007	12551	54574	75854
9	Blake Doe	124 Make it Happen	151 We Did It	New York	Manhattan, NY, 10002	12546	54569	75854
10	John Doe 1	123 Make it Happen	150 We Did It	New York	Manhattan, NY, 10001	12545	54568	75854
11	John Doe 3	125 Make it Happen	152 We Did It	New York	Manhattan, NY, 10003	12547	54570	75854
12	John Doe 4	126 Make it Happen	153 We Did It	New York	Manhattan, NY, 10004	12548	54571	75854
13	John Doe 5	127 Make it Happen	154 We Did It	New York	Manhattan, NY, 10005	12549	54572	75854
14	John Doe 6	128 Make it Happen	155 We Did It	New York	Manhattan, NY, 10006	12550	54573	75854
15	John Doe 8	130 Make it Happen		NYC	Manhattan, NY, 10008	12552	54575	75854
16	John Doe 9	131 Make it Happen		NYC	Manhattan, NY, 10009	12553	54576	75854
17
18
19
Sheet30

 

[Coyotex3]

Will the 1st row to sort always be row 8, or will it always be from the 2nd block of text?

NYC will always be the last range and New York will always be the second to last range.

 

[Fluff]

So you want to sort from the 2nd block to the end of the sheet?

 

[Coyotex3]

So you want to sort from the 2nd block to the end of the sheet?

I think a better way of looking at this is that I want to delete the blanks between NYC an New York, which will always be the last two ranges. I might have one that looks like this some day.

Order Template.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Order Recipient	Primary Address	Secondary Address	City	City, State, Zip Code	Primary Order	Secondary Order	Third Order
2	Michael Doe 1	121 American Eagle	Boston	MA	121 American Eagle, Boston, MA	4512	1015	4045
3	Michael Doe 2	122 American Eagle	Boston	MA	122 American Eagle, Boston, MA	4510	1020	4010
4	Michael Doe 3	123 American Eagle	Boston	MA	123 American Eagle, Boston, MA	4515	1025	4085
5
6
7
8	Jane Doe 1	123 Lets Do It	Getting There	NJ	123 Lets Do it, Getting There, NJ	45612	45123	45699
9	Jane Doe 2	124 Lets Do It	Getting There	NJ	124 Lets Do it, Getting There, NJ	45613	45123	45699
10	Jane Doe 3	125 Lets Do It	Getting There	NJ	125 Lets Do it, Getting There, NJ	45614	45123	45699
11
12
13
14	Ann Doe	129 Make it Happen		NYC	Manhattan, NY, 10007	12551	54574	75854
15	Blake Doe	124 Make it Happen	151 We Did It	New York	Manhattan, NY, 10002	12546	54569	75854
16	John Doe 1	123 Make it Happen	150 We Did It	New York	Manhattan, NY, 10001	12545	54568	75854
17	John Doe 3	125 Make it Happen	152 We Did It	New York	Manhattan, NY, 10003	12547	54570	75854
18	John Doe 4	126 Make it Happen	153 We Did It	New York	Manhattan, NY, 10004	12548	54571	75854
19	John Doe 5	127 Make it Happen	154 We Did It	New York	Manhattan, NY, 10005	12549	54572	75854
20	John Doe 6	128 Make it Happen	155 We Did It	New York	Manhattan, NY, 10006	12550	54573	75854
21	John Doe 8	130 Make it Happen		NYC	Manhattan, NY, 10008	12552	54575	75854
22	John Doe 9	131 Make it Happen		NYC	Manhattan, NY, 10009	12553	54576	75854
23
24
25
26
Sheet30

Ideally Still want to leave NJ and MA alone and only focus on New York and NYC so maybe something like if Range("D").value = "New York" or "NYC" then sort? Don't know if it is possible or not?

 

[Fluff]

That goes against what you have said before & is totally different to what you have shown before.
If you cannot give me straight & accurate answers to my questions, then I cannot help.

 

[Coyotex3]

That goes against what you have said before & is totally different to what you have shown before.
If you cannot give me straight & accurate answers to my questions, then I cannot help.

I really suck at explaining it seems. I will try it once last time and this might help. How can I go from a variable range where New York and NYC will always be at the bottom, delete blank rows between New York and NYC and then Sort.

I want to go from this

Order Template.xlsx
	A	B	C	D	E	F	G	H	I
1	Order Recipient	Primary Address	Secondary Address	City	City, State, Zip Code	Primary Order	Secondary Order	Third Order
2	Michael Doe 1	121 American Eagle	Boston	MA	121 American Eagle, Boston, MA	4512	1015	4045
3	Michael Doe 2	122 American Eagle	Boston	MA	122 American Eagle, Boston, MA	4510	1020	4010
4	Michael Doe 3	123 American Eagle	Boston	MA	123 American Eagle, Boston, MA	4515	1025	4085
5
6
7
8	Jane Doe 1	123 Lets Do It	Getting There	NJ	123 Lets Do it, Getting There, NJ	45612	45123	45699
9	Jane Doe 2	124 Lets Do It	Getting There	NJ	124 Lets Do it, Getting There, NJ	45613	45123	45699
10	Jane Doe 3	125 Lets Do It	Getting There	NJ	125 Lets Do it, Getting There, NJ	45614	45123	45699
11
12
13
14	Blake Doe	124 Make it Happen	151 We Did It	New York	Manhattan, NY, 10002	12546	54569	75854
15	John Doe 1	123 Make it Happen	150 We Did It	New York	Manhattan, NY, 10001	12545	54568	75854
16	John Doe 3	125 Make it Happen	152 We Did It	New York	Manhattan, NY, 10003	12547	54570	75854
17	John Doe 4	126 Make it Happen	153 We Did It	New York	Manhattan, NY, 10004	12548	54571	75854
18	John Doe 5	127 Make it Happen	154 We Did It	New York	Manhattan, NY, 10005	12549	54572	75854
19	John Doe 6	128 Make it Happen	155 We Did It	New York	Manhattan, NY, 10006	12550	54573	75854
20
21
22
23	Ann Doe	129 Make it Happen		NYC	Manhattan, NY, 10007	12551	54574	75854
24	John Doe 8	130 Make it Happen		NYC	Manhattan, NY, 10008	12552	54575	75854
25	John Doe 9	131 Make it Happen		NYC	Manhattan, NY, 10009	12553	54576	75854
26
27
28
Sheet30

To this:

Order Template.xlsx
	A	B	C	D	E	F	G	H	I
1	Order Recipient	Primary Address	Secondary Address	City	City, State, Zip Code	Primary Order	Secondary Order	Third Order
2	Michael Doe 1	121 American Eagle	Boston	MA	121 American Eagle, Boston, MA	4512	1015	4045
3	Michael Doe 2	122 American Eagle	Boston	MA	122 American Eagle, Boston, MA	4510	1020	4010
4	Michael Doe 3	123 American Eagle	Boston	MA	123 American Eagle, Boston, MA	4515	1025	4085
5
6
7
8	Jane Doe 1	123 Lets Do It	Getting There	NJ	123 Lets Do it, Getting There, NJ	45612	45123	45699
9	Jane Doe 2	124 Lets Do It	Getting There	NJ	124 Lets Do it, Getting There, NJ	45613	45123	45699
10	Jane Doe 3	125 Lets Do It	Getting There	NJ	125 Lets Do it, Getting There, NJ	45614	45123	45699
11
12
13
14	Ann Doe	129 Make it Happen		NYC	Manhattan, NY, 10007	12551	54574	75854
15	Blake Doe	124 Make it Happen	151 We Did It	New York	Manhattan, NY, 10002	12546	54569	75854
16	John Doe 1	123 Make it Happen	150 We Did It	New York	Manhattan, NY, 10001	12545	54568	75854
17	John Doe 3	125 Make it Happen	152 We Did It	New York	Manhattan, NY, 10003	12547	54570	75854
18	John Doe 4	126 Make it Happen	153 We Did It	New York	Manhattan, NY, 10004	12548	54571	75854
19	John Doe 5	127 Make it Happen	154 We Did It	New York	Manhattan, NY, 10005	12549	54572	75854
20	John Doe 6	128 Make it Happen	155 We Did It	New York	Manhattan, NY, 10006	12550	54573	75854
21	John Doe 8	130 Make it Happen		NYC	Manhattan, NY, 10008	12552	54575	75854
22	John Doe 9	131 Make it Happen		NYC	Manhattan, NY, 10009	12553	54576	75854
23
24
25
Sheet30

the data needing sorting will always be at the bottom of the excel sheet.

Once again, sorry for the miscommunication on my part. truly appreciate you working with me!

 

[Fluff]

This will sort the last two blocks.

Sub coyotex()
   Dim UsdRws As Long
   
   UsdRws = Range("A" & Rows.Count).End(xlUp).Row
   With Range("A:A").SpecialCells(xlConstants)
      With Range(.Areas(.Areas.Count - 1), Range("A" & UsdRws)).Resize(, 8)
         .Sort .Range("A1"), xlAscending, .Range("G1"), , xlAscending, Header:=xlNo
      End With
   End With
End Sub