delete blank rows based on empty cells into column from sheet to another

[abdo meghari]

HI

I want to copy data from sheet1 to sheet2 . should copy with all format and formula without empty rows. the empty rows depend on column A if any cell is empty in column A where located from row 21 until before row SUB TOTAL ,then should delete it because I will add rows before row SUB TOTAL every time
so any empty row from ROW 21 up to before row SUB TOTAL should delet e it

vv1.xlsm
	A	B	C	D	E	F	G
1						INVOICE
2		COMPANY LTD
3
4						DATE
5						2022/08/02
6						INVOICE
7						BSJD-146
8						CUS
9						CUS-146
10
11	TO:				CONSIGNEE
12	NAME
13	ADDRESS
14	PHONE
15	EMAIL
16
17	OREDER	DATE	FR	SAL		(FOB)	TREM
18	PUR-144
19
20	ITEM	DESCRIBE	TYPE	ORIGIN	QTY	UNIT	TOTAL
21	1	BS 1200R20	G580	THI	200.00	120.00	24,000.00
22	2	BS 1200R20	G580	JAP	230.00	110.00	25300.00
23							0.00
24							0.00
25							0.00
26							0.00
27	SUB TOTAL						49300.00
28	TAX						1873.40
29	FREIGHT						0.00
30	OTHERS						0.00
31	TOTAL						$51,173.40
32	For questions concerning this invoice, please contact
33	Name, (321) 456-7890, Email Address
34	www.yourwebaddress.com
sheet1

Cell Formulas
Range	Formula
F5	F5	=TODAY()
G21:G26	G21	=E21*F21
G27	G27	=SUM(G21:G22)
G28	G28	=SUM(G27)*3.8%
G31	G31	=G27+G28+G29+G30

result
should be

vv1.xlsm
	A	B	C	D	E	F	G
1						INVOICE
2		COMPANY LTD
3
4						DATE
5						2022/08/02
6						INVOICE
7						BSJD-146
8						CUS
9						CUS-146
10
11	TO:				CONSIGNEE
12	NAME
13	ADDRESS
14	PHONE
15	EMAIL
16
17	OREDER	DATE	FR	SAL		(FOB)	TREM
18	PUR-144
19
20	ITEM	DESCRIBE	TYPE	ORIGIN	QTY	UNIT	TOTAL
21	1	BS 1200R20	G580	THI	200.00	120.00	24,000.00
22	2	BS 1200R20	G580	JAP	230.00	110.00	25300.00
23	SUB TOTAL						49300.00
24	TAX						1873.40
25	FREIGHT						0.00
26	OTHERS						0.00
27	TOTAL						$51,173.40
sheet2

Cell Formulas
Range	Formula
F5	F5	=TODAY()
G21:G22	G21	=E21*F21
G23	G23	=SUM(G21:G22)
G24	G24	=SUM(G23)*3.8%
G27	G27	=G23+G24+G25+G26

I hope this clear

 

[Alex Blakenburg]

You're welcome. Glad we could help.

 

[vcoolio]

should copy with all format and formula without empty rows.

Hello Abdo,
My understanding of your opening post is that the empty rows were to be deleted prior to transferring the data to Sheet2.
However, I see that Alex has given you the solution that you wanted so we'll leave it at that.

Cheerio,
vcoolio.