Delete most of all of ranges except specific ranges based on cell value

Abdo

Board Regular
Joined
May 16, 2022
Messages
228
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Hi Experts ,
I need to efficient way by create really fast macro to deal with 10000 rows contains 500 ranges . each range contains some rows .
in LASTROW sheet I search for range in H2 cell by name to match with column C in SPLITTING sheet then should copy the range .
EXAMPLE

DIVIDED1.xlsm
ABCDEFG
1DATEINVOICE NOCLIENT NODESCRIBEDEBITCREDITBALANCE
201/01/2022-ABDEND1OPENNING20,000.00 20,000.00
304/01/2022PA-B3ABDEND1PA20,000.00 40,000.00
405/01/2022SA-B35ABDEND1SA1,000.00 39,000.00
5TOTAL40,000.00 1,000.00 39,000.00
6
7
8DATEINVOICE NOCLIENT NODESCRIBEDEBITCREDITBALANCE
914/01/2022PA-B352ABDEND10PA140.00 140.00
1014/01/2022PA-B352ABDEND10PA100.00 240.00
11TOTAL240.00 240.00
12
13
14DATEINVOICE NOCLIENT NODESCRIBEDEBITCREDITBALANCE
1514/04/2022PA-B442ABDEND100PA1,040.00 1,040.00
16TOTAL1,040.00 1,040.00
17
18
19DATEINVOICE NOCLIENT NODESCRIBEDEBITCREDITBALANCE
2030/09/2024PA-B1342ABDEND1000PA10,040.00 10,040.00
2130/09/2024PA-B1342ABDEND1000SA40.00 10,000.00
2201/10/2024SA1,000.00 9,000.00
23TOTAL10,040.00 9,000.00
24
25
26DATEINVOICE NOCLIENT NODESCRIBEDEBITCREDITBALANCE
2701/10/2024PA-B1343ABDEND1001PA10,050.00 10,050.00
28TOTAL10,050.00 10,050.00
splitting


DIVIDED1.xlsm
ABCDEFGH
1SEARCH NAME
2ABDEND1
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
LASTROW




result

DIVIDED1.xlsm
ABCDEFGH
1DATEINVOICE NOCLIENT NODESCRIBEDEBITCREDITBALANCESEARCH NAME
201/01/2022-ABDEND1OPENNING20,000.00 20,000.00 ABDEND1
304/01/2022PA-B3ABDEND1PA20,000.00 40,000.00
405/01/2022SA-B35ABDEND1SA1,000.00 39,000.00
5TOTAL40,000.00 1,000.00 39,000.00
LASTROW



if I write another name

DIVIDED1.xlsm
ABCDEFGH
1DATEINVOICE NOCLIENT NODESCRIBEDEBITCREDITBALANCESEARCH NAME
201/01/2022-ABDEND1OPENNING20,000.00 20,000.00 ABDEND10
304/01/2022PA-B3ABDEND1PA20,000.00 40,000.00
405/01/2022SA-B35ABDEND1SA1,000.00 39,000.00
5TOTAL40,000.00 1,000.00 39,000.00
LASTROW


will copy to the bottom like this

DIVIDED1.xlsm
ABCDEFGH
1DATEINVOICE NOCLIENT NODESCRIBEDEBITCREDITBALANCESEARCH NAME
201/01/2022-ABDEND1OPENNING20,000.00 20,000.00 ABDEND10
304/01/2022PA-B3ABDEND1PA20,000.00 40,000.00
405/01/2022SA-B35ABDEND1SA1,000.00 39,000.00
5TOTAL40,000.00 1,000.00 39,000.00
6
7
8DATEINVOICE NOCLIENT NODESCRIBEDEBITCREDITBALANCE
914/01/2022PA-B352ABDEND10PA140.00 140.00
1014/01/2022PA-B352ABDEND10PA100.00 240.00
11TOTAL240.00 240.00
12
13
14
15
16
17
18
19
20
21
22
23
LASTROW


and if I repeat writing the same name in H2 cell has already copy like this
DIVIDED1.xlsm
ABCDEFGH
1DATEINVOICE NOCLIENT NODESCRIBEDEBITCREDITBALANCESEARCH NAME
201/01/2022-ABDEND1OPENNING20,000.00 20,000.00 ABDEND1
304/01/2022PA-B3ABDEND1PA20,000.00 40,000.00
405/01/2022SA-B35ABDEND1SA1,000.00 39,000.00
5TOTAL40,000.00 1,000.00 39,000.00
6
7
8DATEINVOICE NOCLIENT NODESCRIBEDEBITCREDITBALANCE
914/01/2022PA-B352ABDEND10PA140.00 140.00
1014/01/2022PA-B352ABDEND10PA100.00 240.00
11TOTAL240.00 240.00
LASTROW


then should pops message " this name has already existed, you can't repeat copying" and exit without repeat copying the same name.

and if the H2 is empty as like this
DIVIDED1.xlsm
ABCDEFGH
1DATEINVOICE NOCLIENT NODESCRIBEDEBITCREDITBALANCESEARCH NAME
201/01/2022-ABDEND1OPENNING20,000.00 20,000.00
304/01/2022PA-B3ABDEND1PA20,000.00 40,000.00
405/01/2022SA-B35ABDEND1SA1,000.00 39,000.00
5TOTAL40,000.00 1,000.00 39,000.00
6
7
8DATEINVOICE NOCLIENT NODESCRIBEDEBITCREDITBALANCE
914/01/2022PA-B352ABDEND10PA140.00 140.00
1014/01/2022PA-B352ABDEND10PA100.00 240.00
11TOTAL240.00 240.00
LASTROW



then the the result should delete all of ranges like this
DIVIDED1.xlsm
ABCDEFGH
1SEARCH NAME
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
LASTROW

thanks
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top