VBA code for row deletion and change to general format for left over cell

[sambharris6]

Hi Rocket Scientists!

Help is much appreciated, love you guys!!!!!!!!!

So, I have journal entries in succession cell A8, A39...etc, each JE will have varying degrees of rows filled out (i.e JE 1 has 4 lines, JE 2 has 6 lines), each JE has room for 22 JE rows. What I'd like to do is 2 fold:

1) Delete excess rows (i.e. yellow rows)
2) After deleted yellow rows keep a space between the last JE line (i.e. row 21, row 54 so on in red), and proceeding JE, for example Row 39 would become Row 22 with space between
3) In row 21, row 54 change the format from a "date" format to a "general" format cell O21 & O54 presently in black and ensuring that the date values above it stay in "date" format.

****NOTE - the colors shouldn't be apart of the VBA they will not be present (i.e. yellow, red, black) just to illustrate for you guys.

SAP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Upload General Journal Entry
2	// To add field columns to the template, please add technical names.
3	// For a complete list of field columns and their technical names, choose ? in the right upper corner of the app screen and then view the Browse entry of web assistance.
4	// If you want to add more Profitability Segment (CO-PA) fields, make sure that you add the ‘PROF_’ prefix in the technical names of the CO-PA fields.
5	Batch ID
6
7
8	1	Header
9		BUKRS	BLART	BLDAT	BUDAT	MONAT	BKTXT	WAERS	LDGRP	KURSF_EXT	WWERT	XBLNR	PARGB_HDR	XMWST
10		*Company Code (4)	*Journal Entry Type (2)	*Journal Entry Date	*Posting Date	Fiscal period (2)	Document Header Text (25)	*Transaction Currency (5)	Ledger Group (4)	Exchange Rate (12)	Currency Translation Date	Reference Document Number (16)	Partner Business Area (4)	Calculate Tax Automatically (1)
11		2930	SA	2021-02-28	2021-02-28		FEB 2021	CAD				FEB 2021		X
12
13		Line Items
14					Transaction Currency
15		BUKRS	HKONT	SGTXT	WRSOL	WRHAB	DMBTR	DMBE2	MWSKZ	TXJCD	KOSTL	PRCTR	AUFNR	PS_POSID	VALUT	HBKID	HKTID	ZUONR	VBUND	SEGMENT	PROF_KNDNR	PROF_ARTNR	PROF_KUNRE
16		Company Code (4)	G/L Account (10)	Item Text (50)	Debit	Credit	Amount in Company Code Currency	Amount in second local currency (LC2)	Tax Code (2)	Tax Jurisdiction (15)	Cost Center (10)	Profit Center (10)	Order Number (12)	WBS Element (24)	Value Date	House Bank (5)	House Bank Account (5)	Assignment number (18)	Trading Partner (6)	Segment for Segmental Reporting (10)	Customer (10)	Product number (40)	Bill-To Party (10)
17		2930	16009100	Journal Entry Descriptions	100				P0	CAON					2021-02-28
18		2930	16009100	Journal Entry Descriptions		100			P0	CAON					2021-02-28
19		2930	21725000	Journal Entry Descriptions		100			P0	CAON					2021-02-28
20		2930	21725000	Journal Entry Descriptions	100				P0	CAON					2021-02-28
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39	2	Header
40		BUKRS	BLART	BLDAT	BUDAT	MONAT	BKTXT	WAERS	LDGRP	KURSF_EXT	WWERT	XBLNR	PARGB_HDR	XMWST
41		*Company Code (4)	*Journal Entry Type (2)	*Journal Entry Date	*Posting Date	Fiscal period (2)	Document Header Text (25)	*Transaction Currency (5)	Ledger Group (4)	Exchange Rate (12)	Currency Translation Date	Reference Document Number (16)	Partner Business Area (4)	Calculate Tax Automatically (1)
42		2920	SA	2021-02-28	2021-02-28		FEB 2021	CAD				FEB 2021		X
43
44		Line Items
45					Transaction Currency
46		BUKRS	HKONT	SGTXT	WRSOL	WRHAB	DMBTR	DMBE2	MWSKZ	TXJCD	KOSTL	PRCTR	AUFNR	PS_POSID	VALUT	HBKID	HKTID	ZUONR	VBUND	SEGMENT	PROF_KNDNR	PROF_ARTNR	PROF_KUNRE
47		Company Code (4)	G/L Account (10)	Item Text (50)	Debit	Credit	Amount in Company Code Currency	Amount in second local currency (LC2)	Tax Code (2)	Tax Jurisdiction (15)	Cost Center (10)	Profit Center (10)	Order Number (12)	WBS Element (24)	Value Date	House Bank (5)	House Bank Account (5)	Assignment number (18)	Trading Partner (6)	Segment for Segmental Reporting (10)	Customer (10)	Product number (40)	Bill-To Party (10)
48		2920	13711400	Journal Entry Descriptions	100				P0	CAON					2021-02-28
49		2920	21541000	Journal Entry Descriptions		100			P0	CAON					2021-02-28
50		2920	21541000	Journal Entry Descriptions		100			P0	CAON					2021-02-28
51		2920	41002000	Journal Entry Descriptions		100			P0	CAON					2021-02-28
52		2920	41002000	Journal Entry Descriptions	100				P0	CAON					2021-02-28
53		2920	41002000	Journal Entry Descriptions	100				P0	CAON					2021-02-28
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70	3	Header
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A48,A53,A58,A63	Cell Value	duplicates	text	NO
A17,A22,A27,A32	Cell Value	duplicates	text	NO
A70	Cell Value	duplicates	text	NO
A8:A16	Cell Value	duplicates	text	NO
A1:A7	Cell Value	duplicates	text	NO
A39:A47	Cell Value	duplicates	text	NO

 

[sambharris6]

Hi Rocket Scientists!

Help is much appreciated, love you guys!!!!!!!!!

So, I have journal entries in succession cell A8, A39...etc, each JE will have varying degrees of rows filled out (i.e JE 1 has 4 lines, JE 2 has 6 lines), each JE has room for 22 JE rows. What I'd like to do is 2 fold:

1) Delete excess rows (i.e. yellow rows)
2) After deleted yellow rows keep a space between the last JE line (i.e. row 21, row 54 so on in red), and proceeding JE, for example Row 39 would become Row 22 with space between
3) In row 21, row 54 change the format from a "date" format to a "general" format cell O21 & O54 presently in black and ensuring that the date values above it stay in "date" format.

****NOTE - the colors shouldn't be apart of the VBA they will not be present (i.e. yellow, red, black) just to illustrate for you guys.

SAP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Upload General Journal Entry
2	// To add field columns to the template, please add technical names.
3	// For a complete list of field columns and their technical names, choose ? in the right upper corner of the app screen and then view the Browse entry of web assistance.
4	// If you want to add more Profitability Segment (CO-PA) fields, make sure that you add the ‘PROF_’ prefix in the technical names of the CO-PA fields.
5	Batch ID
6
7
8	1	Header
9		BUKRS	BLART	BLDAT	BUDAT	MONAT	BKTXT	WAERS	LDGRP	KURSF_EXT	WWERT	XBLNR	PARGB_HDR	XMWST
10		*Company Code (4)	*Journal Entry Type (2)	*Journal Entry Date	*Posting Date	Fiscal period (2)	Document Header Text (25)	*Transaction Currency (5)	Ledger Group (4)	Exchange Rate (12)	Currency Translation Date	Reference Document Number (16)	Partner Business Area (4)	Calculate Tax Automatically (1)
11		2930	SA	2021-02-28	2021-02-28		FEB 2021	CAD				FEB 2021		X
12
13		Line Items
14					Transaction Currency
15		BUKRS	HKONT	SGTXT	WRSOL	WRHAB	DMBTR	DMBE2	MWSKZ	TXJCD	KOSTL	PRCTR	AUFNR	PS_POSID	VALUT	HBKID	HKTID	ZUONR	VBUND	SEGMENT	PROF_KNDNR	PROF_ARTNR	PROF_KUNRE
16		Company Code (4)	G/L Account (10)	Item Text (50)	Debit	Credit	Amount in Company Code Currency	Amount in second local currency (LC2)	Tax Code (2)	Tax Jurisdiction (15)	Cost Center (10)	Profit Center (10)	Order Number (12)	WBS Element (24)	Value Date	House Bank (5)	House Bank Account (5)	Assignment number (18)	Trading Partner (6)	Segment for Segmental Reporting (10)	Customer (10)	Product number (40)	Bill-To Party (10)
17		2930	16009100	Journal Entry Descriptions	100				P0	CAON					2021-02-28
18		2930	16009100	Journal Entry Descriptions		100			P0	CAON					2021-02-28
19		2930	21725000	Journal Entry Descriptions		100			P0	CAON					2021-02-28
20		2930	21725000	Journal Entry Descriptions	100				P0	CAON					2021-02-28
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39	2	Header
40		BUKRS	BLART	BLDAT	BUDAT	MONAT	BKTXT	WAERS	LDGRP	KURSF_EXT	WWERT	XBLNR	PARGB_HDR	XMWST
41		*Company Code (4)	*Journal Entry Type (2)	*Journal Entry Date	*Posting Date	Fiscal period (2)	Document Header Text (25)	*Transaction Currency (5)	Ledger Group (4)	Exchange Rate (12)	Currency Translation Date	Reference Document Number (16)	Partner Business Area (4)	Calculate Tax Automatically (1)
42		2920	SA	2021-02-28	2021-02-28		FEB 2021	CAD				FEB 2021		X
43
44		Line Items
45					Transaction Currency
46		BUKRS	HKONT	SGTXT	WRSOL	WRHAB	DMBTR	DMBE2	MWSKZ	TXJCD	KOSTL	PRCTR	AUFNR	PS_POSID	VALUT	HBKID	HKTID	ZUONR	VBUND	SEGMENT	PROF_KNDNR	PROF_ARTNR	PROF_KUNRE
47		Company Code (4)	G/L Account (10)	Item Text (50)	Debit	Credit	Amount in Company Code Currency	Amount in second local currency (LC2)	Tax Code (2)	Tax Jurisdiction (15)	Cost Center (10)	Profit Center (10)	Order Number (12)	WBS Element (24)	Value Date	House Bank (5)	House Bank Account (5)	Assignment number (18)	Trading Partner (6)	Segment for Segmental Reporting (10)	Customer (10)	Product number (40)	Bill-To Party (10)
48		2920	13711400	Journal Entry Descriptions	100				P0	CAON					2021-02-28
49		2920	21541000	Journal Entry Descriptions		100			P0	CAON					2021-02-28
50		2920	21541000	Journal Entry Descriptions		100			P0	CAON					2021-02-28
51		2920	41002000	Journal Entry Descriptions		100			P0	CAON					2021-02-28
52		2920	41002000	Journal Entry Descriptions	100				P0	CAON					2021-02-28
53		2920	41002000	Journal Entry Descriptions	100				P0	CAON					2021-02-28
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70	3	Header
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A48,A53,A58,A63	Cell Value	duplicates	text	NO
A17,A22,A27,A32	Cell Value	duplicates	text	NO
A70	Cell Value	duplicates	text	NO
A8:A16	Cell Value	duplicates	text	NO
A1:A7	Cell Value	duplicates	text	NO
A39:A47	Cell Value	duplicates	text	NO

Hi Rocket Scientists!

Help is much appreciated, love you guys!!!!!!!!!

So, I have journal entries in succession cell A8, A39...etc, each JE will have varying degrees of rows filled out (i.e JE 1 has 4 lines, JE 2 has 6 lines), each JE has room for 22 JE rows. What I'd like to do is 2 fold:

1) Delete excess rows (i.e. yellow rows)
2) After deleted yellow rows keep a space between the last JE line (i.e. row 21, row 54 so on in red), and proceeding JE, for example Row 39 would become Row 22 with space between
3) In row 21, row 54 change the format from a "date" format to a "general" format cell O21 & O54 presently in black and ensuring that the date values above it stay in "date" format.

****NOTE - the colors shouldn't be apart of the VBA they will not be present (i.e. yellow, red, black) just to illustrate for you guys.

SAP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Upload General Journal Entry
2	// To add field columns to the template, please add technical names.
3	// For a complete list of field columns and their technical names, choose ? in the right upper corner of the app screen and then view the Browse entry of web assistance.
4	// If you want to add more Profitability Segment (CO-PA) fields, make sure that you add the ‘PROF_’ prefix in the technical names of the CO-PA fields.
5	Batch ID
6
7
8	1	Header
9		BUKRS	BLART	BLDAT	BUDAT	MONAT	BKTXT	WAERS	LDGRP	KURSF_EXT	WWERT	XBLNR	PARGB_HDR	XMWST
10		*Company Code (4)	*Journal Entry Type (2)	*Journal Entry Date	*Posting Date	Fiscal period (2)	Document Header Text (25)	*Transaction Currency (5)	Ledger Group (4)	Exchange Rate (12)	Currency Translation Date	Reference Document Number (16)	Partner Business Area (4)	Calculate Tax Automatically (1)
11		2930	SA	2021-02-28	2021-02-28		FEB 2021	CAD				FEB 2021		X
12
13		Line Items
14					Transaction Currency
15		BUKRS	HKONT	SGTXT	WRSOL	WRHAB	DMBTR	DMBE2	MWSKZ	TXJCD	KOSTL	PRCTR	AUFNR	PS_POSID	VALUT	HBKID	HKTID	ZUONR	VBUND	SEGMENT	PROF_KNDNR	PROF_ARTNR	PROF_KUNRE
16		Company Code (4)	G/L Account (10)	Item Text (50)	Debit	Credit	Amount in Company Code Currency	Amount in second local currency (LC2)	Tax Code (2)	Tax Jurisdiction (15)	Cost Center (10)	Profit Center (10)	Order Number (12)	WBS Element (24)	Value Date	House Bank (5)	House Bank Account (5)	Assignment number (18)	Trading Partner (6)	Segment for Segmental Reporting (10)	Customer (10)	Product number (40)	Bill-To Party (10)
17		2930	16009100	Journal Entry Descriptions	100				P0	CAON					2021-02-28
18		2930	16009100	Journal Entry Descriptions		100			P0	CAON					2021-02-28
19		2930	21725000	Journal Entry Descriptions		100			P0	CAON					2021-02-28
20		2930	21725000	Journal Entry Descriptions	100				P0	CAON					2021-02-28
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39	2	Header
40		BUKRS	BLART	BLDAT	BUDAT	MONAT	BKTXT	WAERS	LDGRP	KURSF_EXT	WWERT	XBLNR	PARGB_HDR	XMWST
41		*Company Code (4)	*Journal Entry Type (2)	*Journal Entry Date	*Posting Date	Fiscal period (2)	Document Header Text (25)	*Transaction Currency (5)	Ledger Group (4)	Exchange Rate (12)	Currency Translation Date	Reference Document Number (16)	Partner Business Area (4)	Calculate Tax Automatically (1)
42		2920	SA	2021-02-28	2021-02-28		FEB 2021	CAD				FEB 2021		X
43
44		Line Items
45					Transaction Currency
46		BUKRS	HKONT	SGTXT	WRSOL	WRHAB	DMBTR	DMBE2	MWSKZ	TXJCD	KOSTL	PRCTR	AUFNR	PS_POSID	VALUT	HBKID	HKTID	ZUONR	VBUND	SEGMENT	PROF_KNDNR	PROF_ARTNR	PROF_KUNRE
47		Company Code (4)	G/L Account (10)	Item Text (50)	Debit	Credit	Amount in Company Code Currency	Amount in second local currency (LC2)	Tax Code (2)	Tax Jurisdiction (15)	Cost Center (10)	Profit Center (10)	Order Number (12)	WBS Element (24)	Value Date	House Bank (5)	House Bank Account (5)	Assignment number (18)	Trading Partner (6)	Segment for Segmental Reporting (10)	Customer (10)	Product number (40)	Bill-To Party (10)
48		2920	13711400	Journal Entry Descriptions	100				P0	CAON					2021-02-28
49		2920	21541000	Journal Entry Descriptions		100			P0	CAON					2021-02-28
50		2920	21541000	Journal Entry Descriptions		100			P0	CAON					2021-02-28
51		2920	41002000	Journal Entry Descriptions		100			P0	CAON					2021-02-28
52		2920	41002000	Journal Entry Descriptions	100				P0	CAON					2021-02-28
53		2920	41002000	Journal Entry Descriptions	100				P0	CAON					2021-02-28
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70	3	Header
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A48,A53,A58,A63	Cell Value	duplicates	text	NO
A17,A22,A27,A32	Cell Value	duplicates	text	NO
A70	Cell Value	duplicates	text	NO
A8:A16	Cell Value	duplicates	text	NO
A1:A7	Cell Value	duplicates	text	NO
A39:A47	Cell Value	duplicates	text	NO

Sub Empty_Journals()
Dim rngArea As Range
Application.ScreenUpdating = False
For Each rngArea In Range("B8", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).Areas
If rngArea.Cells.Count >= 2 Then
rngArea.EntireRow.Range("O1").NumberFormat = "General"
rngArea.Offset(1).Resize(rngArea.Cells.Count - 1).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub




Here's a solution but can we delete journal entries that exhbit no entry (i.e. blank))