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

S

sambharris6

New Member
Joined
Mar 22, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVW
1Upload 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.
5Batch ID
6
7
81Header
9BUKRSBLARTBLDATBUDATMONATBKTXTWAERSLDGRPKURSF_EXTWWERTXBLNRPARGB_HDRXMWST
10*Company Code (4)*Journal Entry Type (2)*Journal Entry Date*Posting DateFiscal period (2)Document Header Text (25)*Transaction Currency (5)Ledger Group (4)Exchange Rate (12)Currency Translation DateReference Document Number (16)Partner Business Area (4)Calculate Tax Automatically (1)
112930SA2021-02-282021-02-28FEB 2021CADFEB 2021X
12
13Line Items
14Transaction Currency
15BUKRSHKONTSGTXTWRSOLWRHABDMBTRDMBE2MWSKZTXJCDKOSTLPRCTRAUFNRPS_POSIDVALUTHBKIDHKTIDZUONRVBUNDSEGMENTPROF_KNDNRPROF_ARTNRPROF_KUNRE
16Company Code (4)G/L Account (10)Item Text (50)DebitCreditAmount in Company Code CurrencyAmount in second local currency (LC2)Tax Code (2)Tax Jurisdiction (15)Cost Center (10)Profit Center (10)Order Number (12)WBS Element (24)Value DateHouse 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)
17293016009100Journal Entry Descriptions100P0CAON2021-02-28
18293016009100Journal Entry Descriptions100P0CAON2021-02-28
19293021725000Journal Entry Descriptions100P0CAON2021-02-28
20293021725000Journal Entry Descriptions100P0CAON2021-02-28
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
392Header
40BUKRSBLARTBLDATBUDATMONATBKTXTWAERSLDGRPKURSF_EXTWWERTXBLNRPARGB_HDRXMWST
41*Company Code (4)*Journal Entry Type (2)*Journal Entry Date*Posting DateFiscal period (2)Document Header Text (25)*Transaction Currency (5)Ledger Group (4)Exchange Rate (12)Currency Translation DateReference Document Number (16)Partner Business Area (4)Calculate Tax Automatically (1)
422920SA2021-02-282021-02-28FEB 2021CADFEB 2021X
43
44Line Items
45Transaction Currency
46BUKRSHKONTSGTXTWRSOLWRHABDMBTRDMBE2MWSKZTXJCDKOSTLPRCTRAUFNRPS_POSIDVALUTHBKIDHKTIDZUONRVBUNDSEGMENTPROF_KNDNRPROF_ARTNRPROF_KUNRE
47Company Code (4)G/L Account (10)Item Text (50)DebitCreditAmount in Company Code CurrencyAmount in second local currency (LC2)Tax Code (2)Tax Jurisdiction (15)Cost Center (10)Profit Center (10)Order Number (12)WBS Element (24)Value DateHouse 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)
48292013711400Journal Entry Descriptions100P0CAON2021-02-28
49292021541000Journal Entry Descriptions100P0CAON2021-02-28
50292021541000Journal Entry Descriptions100P0CAON2021-02-28
51292041002000Journal Entry Descriptions100P0CAON2021-02-28
52292041002000Journal Entry Descriptions100P0CAON2021-02-28
53292041002000Journal Entry Descriptions100P0CAON2021-02-28
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
703Header
Data
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A48,A53,A58,A63Cell ValueduplicatestextNO
A17,A22,A27,A32Cell ValueduplicatestextNO
A70Cell ValueduplicatestextNO
A8:A16Cell ValueduplicatestextNO
A1:A7Cell ValueduplicatestextNO
A39:A47Cell ValueduplicatestextNO
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
sambharris6 said:
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
ABCDEFGHIJKLMNOPQRSTUVW
1Upload 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.
5Batch ID
6
7
81Header
9BUKRSBLARTBLDATBUDATMONATBKTXTWAERSLDGRPKURSF_EXTWWERTXBLNRPARGB_HDRXMWST
10*Company Code (4)*Journal Entry Type (2)*Journal Entry Date*Posting DateFiscal period (2)Document Header Text (25)*Transaction Currency (5)Ledger Group (4)Exchange Rate (12)Currency Translation DateReference Document Number (16)Partner Business Area (4)Calculate Tax Automatically (1)
112930SA2021-02-282021-02-28FEB 2021CADFEB 2021X
12
13Line Items
14Transaction Currency
15BUKRSHKONTSGTXTWRSOLWRHABDMBTRDMBE2MWSKZTXJCDKOSTLPRCTRAUFNRPS_POSIDVALUTHBKIDHKTIDZUONRVBUNDSEGMENTPROF_KNDNRPROF_ARTNRPROF_KUNRE
16Company Code (4)G/L Account (10)Item Text (50)DebitCreditAmount in Company Code CurrencyAmount in second local currency (LC2)Tax Code (2)Tax Jurisdiction (15)Cost Center (10)Profit Center (10)Order Number (12)WBS Element (24)Value DateHouse 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)
17293016009100Journal Entry Descriptions100P0CAON2021-02-28
18293016009100Journal Entry Descriptions100P0CAON2021-02-28
19293021725000Journal Entry Descriptions100P0CAON2021-02-28
20293021725000Journal Entry Descriptions100P0CAON2021-02-28
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
392Header
40BUKRSBLARTBLDATBUDATMONATBKTXTWAERSLDGRPKURSF_EXTWWERTXBLNRPARGB_HDRXMWST
41*Company Code (4)*Journal Entry Type (2)*Journal Entry Date*Posting DateFiscal period (2)Document Header Text (25)*Transaction Currency (5)Ledger Group (4)Exchange Rate (12)Currency Translation DateReference Document Number (16)Partner Business Area (4)Calculate Tax Automatically (1)
422920SA2021-02-282021-02-28FEB 2021CADFEB 2021X
43
44Line Items
45Transaction Currency
46BUKRSHKONTSGTXTWRSOLWRHABDMBTRDMBE2MWSKZTXJCDKOSTLPRCTRAUFNRPS_POSIDVALUTHBKIDHKTIDZUONRVBUNDSEGMENTPROF_KNDNRPROF_ARTNRPROF_KUNRE
47Company Code (4)G/L Account (10)Item Text (50)DebitCreditAmount in Company Code CurrencyAmount in second local currency (LC2)Tax Code (2)Tax Jurisdiction (15)Cost Center (10)Profit Center (10)Order Number (12)WBS Element (24)Value DateHouse 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)
48292013711400Journal Entry Descriptions100P0CAON2021-02-28
49292021541000Journal Entry Descriptions100P0CAON2021-02-28
50292021541000Journal Entry Descriptions100P0CAON2021-02-28
51292041002000Journal Entry Descriptions100P0CAON2021-02-28
52292041002000Journal Entry Descriptions100P0CAON2021-02-28
53292041002000Journal Entry Descriptions100P0CAON2021-02-28
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
703Header
Data
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A48,A53,A58,A63Cell ValueduplicatestextNO
A17,A22,A27,A32Cell ValueduplicatestextNO
A70Cell ValueduplicatestextNO
A8:A16Cell ValueduplicatestextNO
A1:A7Cell ValueduplicatestextNO
A39:A47Cell ValueduplicatestextNO
Click to expand...
sambharris6 said:
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
ABCDEFGHIJKLMNOPQRSTUVW
1Upload 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.
5Batch ID
6
7
81Header
9BUKRSBLARTBLDATBUDATMONATBKTXTWAERSLDGRPKURSF_EXTWWERTXBLNRPARGB_HDRXMWST
10*Company Code (4)*Journal Entry Type (2)*Journal Entry Date*Posting DateFiscal period (2)Document Header Text (25)*Transaction Currency (5)Ledger Group (4)Exchange Rate (12)Currency Translation DateReference Document Number (16)Partner Business Area (4)Calculate Tax Automatically (1)
112930SA2021-02-282021-02-28FEB 2021CADFEB 2021X
12
13Line Items
14Transaction Currency
15BUKRSHKONTSGTXTWRSOLWRHABDMBTRDMBE2MWSKZTXJCDKOSTLPRCTRAUFNRPS_POSIDVALUTHBKIDHKTIDZUONRVBUNDSEGMENTPROF_KNDNRPROF_ARTNRPROF_KUNRE
16Company Code (4)G/L Account (10)Item Text (50)DebitCreditAmount in Company Code CurrencyAmount in second local currency (LC2)Tax Code (2)Tax Jurisdiction (15)Cost Center (10)Profit Center (10)Order Number (12)WBS Element (24)Value DateHouse 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)
17293016009100Journal Entry Descriptions100P0CAON2021-02-28
18293016009100Journal Entry Descriptions100P0CAON2021-02-28
19293021725000Journal Entry Descriptions100P0CAON2021-02-28
20293021725000Journal Entry Descriptions100P0CAON2021-02-28
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
392Header
40BUKRSBLARTBLDATBUDATMONATBKTXTWAERSLDGRPKURSF_EXTWWERTXBLNRPARGB_HDRXMWST
41*Company Code (4)*Journal Entry Type (2)*Journal Entry Date*Posting DateFiscal period (2)Document Header Text (25)*Transaction Currency (5)Ledger Group (4)Exchange Rate (12)Currency Translation DateReference Document Number (16)Partner Business Area (4)Calculate Tax Automatically (1)
422920SA2021-02-282021-02-28FEB 2021CADFEB 2021X
43
44Line Items
45Transaction Currency
46BUKRSHKONTSGTXTWRSOLWRHABDMBTRDMBE2MWSKZTXJCDKOSTLPRCTRAUFNRPS_POSIDVALUTHBKIDHKTIDZUONRVBUNDSEGMENTPROF_KNDNRPROF_ARTNRPROF_KUNRE
47Company Code (4)G/L Account (10)Item Text (50)DebitCreditAmount in Company Code CurrencyAmount in second local currency (LC2)Tax Code (2)Tax Jurisdiction (15)Cost Center (10)Profit Center (10)Order Number (12)WBS Element (24)Value DateHouse 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)
48292013711400Journal Entry Descriptions100P0CAON2021-02-28
49292021541000Journal Entry Descriptions100P0CAON2021-02-28
50292021541000Journal Entry Descriptions100P0CAON2021-02-28
51292041002000Journal Entry Descriptions100P0CAON2021-02-28
52292041002000Journal Entry Descriptions100P0CAON2021-02-28
53292041002000Journal Entry Descriptions100P0CAON2021-02-28
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
703Header
Data
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A48,A53,A58,A63Cell ValueduplicatestextNO
A17,A22,A27,A32Cell ValueduplicatestextNO
A70Cell ValueduplicatestextNO
A8:A16Cell ValueduplicatestextNO
A1:A7Cell ValueduplicatestextNO
A39:A47Cell ValueduplicatestextNO
Click to expand...
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))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
355
Jagadeshrao
J
K
  • Question Question
Looking for assistance using MS Excel VBA to hide rows
Replies
4
Views
517
kcampbell675
K
X
  • Solved
Problems when i add new rows in table via userform. Formulas wont get taken over and cell formats are changing
Replies
7
Views
569
Anthony47
A
U
  • Question Question
Excel VBA: Combine Row Data of Specific Accounts
Replies
2
Views
524
unknownymous
U
P
  • Solved
How to copy and paste the next row VBA Help.
Replies
8
Views
541
James006
James006

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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