Can Excel execute this complex value recheck with rows being deleted?

[Coyotex3]

So, I would like to tell excel to look at the value in row(Where it says Cash Available) in this case "H16" and if the value is less than the value above it "H15" to delete where Range("D") = "Okinawa" and Range("A") = OsaCon, starting by oldest date "Range("E") reexamine to see if value in "H16" is greater than the value in "H15."

Thank you.

 

[johnnyL]

@Coyotex3, Can you please use XL2BB to post your examples? A lot of people are not going to take the time to type in all of the info that you are posting via pics just to try and assist you. If you use the XL2BB add in, others can just copy and paste your info that you provided and then start looking into assisting you.

That being said, can you post a better example, before & after?

I ask because I don't think you want to do what you have said. Maybe you can rephrase what you want to do in addition to showing a better before and after.

 

[Coyotex3]

@Coyotex3, Can you please use XL2BB to post your examples? A lot of people are not going to take the time to type in all of the info that you are posting via pics just to try and assist you. If you use the XL2BB add in, others can just copy and paste your info that you provided and then start looking into assisting you.

That being said, can you post a better example, before & after?

I ask because I don't think you want to do what you have said. Maybe you can rephrase what you want to do in addition to showing a better before and after.

Hey what is XL2BB ?

 

[johnnyL]

XL2BB

Download link and directions

 

[Coyotex3]

XL2BB

Download link and directions

Practice(16911).xlsx
	A	B	C	D	E	F	G	H	I
1	Exported Report
2	usa
3	Period:
4	As of :
5	Code	Person	Notes	Location	Invoice Date	Description	Invoice #	Amount	Notes
6	FukCon	Fukuoka Construction	Bridge	Fukuoka	10/2/2021	00000000-Construction	9	20,000.00	Bridge
7	FukCon	Fukuoka Construction	Bridge	Fukuoka	10/2/2021	00000000-Construction	18	20,000.00	Bridge
8							Account Payables	40,000.00
9							Cash Availables	60,000.00
10
11	FukCon	Fukuoka Construction	Bridge	Okinawa	9/30/2021	00000000-Construction	16	20,000.00	Bridge
12	OkiCon	Okinawa Construction	New Building	Okinawa	12/5/2021	000000000- Construction	1	50,000.00	New Building
13	OkiCon	Okinawa Construction	New Building	Okinawa	12/6/2021	000000000- Construction	10	50,000.00	New Building
14	OsaCon	Osaka Construction	Been Paid	Okinawa	7/1/2021	00000000-Construction	5	50,000.00	WIP
15	OsaCon	Osaka Construction	Been Paid	Okinawa	12/15/2020	00000000-Construction	14	60,000.00	WIP
16							Account Payables	230,000.00
17							Cash Availables	120,000.00
18
19	OsaCon	Osaka Construction	Been Paid	Osaka	8/2/2021	00000000-Construction	3	50,000.00	WIP
20	OsaCon	Osaka Construction	Been Paid	Osaka	8/2/2021	00000000-Construction	12	50,000.00	WIP
21							Account Payables	100,000.00
22							Cash Availables	200,000.00
23
24	FukCon	Fukuoka Construction	Bridge	T+8:20okyo	10/1/2021	00000000-Construction	8	20,000.00	Bridge
25	FukCon	Fukuoka Construction	Bridge	T+8:20okyo	10/1/2021	00000000-Construction	17	20,000.00	Bridge
26							Account Payables	40,000.00
27							Cash Availables	40,000.00
28
29	OsaCon	Osaka Construction	Been Paid	Tokyo	8/5/2021	00000000-Construction	4	50,000.00	WIP
30	OsaCon	Osaka Construction	Been Paid	Tokyo	8/5/2021	00000000-Construction	13	50,000.00	WIP
31	TokCon	Tokyo Construction	Building Renewal	Tokyo	12/4/2021	00000000-Construction	2	5,000.00	Renewal
32	TokCon	Tokyo Construction	Building Renewal	Tokyo	12/4/2021	00000000-Construction	11	5,000.00	Renewal
33							Account Payables	110,000.00
34							Cash Availables	200,000.00
35
Report1

Cell Formulas
Range	Formula
H8,H26,H21	H8	=SUM(H6:H7)
H16	H16	=SUM(H11:H15)
H27	H27	=SUM(H26)
H33	H33	=SUM(H29:H32)

Does this work?

I would like to tell excel to look at the value in row(Where it says Cash Available) in this case "H17" and if the value is less than the value above it "H16" to delete where Range("D") = "Okinawa" and Range("A") = OsaCon, starting by oldest date "Range("E") reexamine to see if value in "H17" is greater than the value in "H16."

 

[johnnyL]

What do you want the final result to look like?

 

[Coyotex3]

What do you want the final result to look like?

Hey JohnnyL, thank you for working with me on this once again. For my end result I would like for the code to delete row 16 since it meets the requirements of "Range D = Okinawa" and "Range A = OsaCon" and it is the oldest invoice. After that I want it to reexamine and check if "H17" is greater or equal to "H16" if so then exit sub. If the value in "H17" is less than "H16" delete the next range that meets the parameter etc.

I know this is a bit complicated since I would like for the system to edit the Auto Sum values and ranges.

The end goal is to build onto this in order to get it to look for different keywords in different ranges. Hope this helps clarify things a bit more.

 

[johnnyL]

Ok, I tried to ask you for clarification and you didn't give me what I was trying to find out so try the following and see if it does what you want:

Sub DeleteRowPerTests()
'
    Dim OldestDate          As Date
    Dim PreviousRow         As Long
    Dim LastRowInColumnG    As Long
    Dim OffsetColumn        As Long
    Dim OffsetRow           As Long
    Dim RowToDelete         As Long
    Dim StartRowData        As Long
    Dim cell                As Range
'
    StartRowData = 6
'
    LastRowInColumnG = Range("G" & Rows.Count).End(xlUp).Row                                ' Get last row # used in column G
'
StartOfForEachLoop:
    For Each cell In Range("G" & StartRowData & ":G" & LastRowInColumnG)                    ' Loop through the G column
        OffsetColumn = 0
        OffsetRow = 0
'
        If cell.Value = "Cash Available" And cell.Offset(OffsetRow, OffsetColumn + 1) <> vbNullString And _
            cell.Offset(OffsetRow, OffsetColumn + 1) < cell.Offset(OffsetRow - 1, OffsetColumn + 1) And _
            cell.Offset(OffsetRow - 2, OffsetColumn - 3) = "Okinawa" _
            And cell.Offset(OffsetRow - 2, OffsetColumn - 6) = "OsaCon" Then                '   If all conditions are met then ...
'
            OldestDate = cell.Offset(OffsetRow - 2, OffsetColumn - 2).Value2                '       Save the OldestDate
            RowToDelete = cell.Offset(OffsetRow - 2, OffsetColumn - 2).Row                  '       Save the RowToDelete for the OldestDate
'
            For PreviousRow = 3 To RowToDelete - StartRowData
                If cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 3) = "Okinawa" And _
                    cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 6) = "OsaCon" Then  '           If both other conditions are met then ...
                        If cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 2).Value2 < OldestDate Then  '               If older date found then ...
                            OldestDate = cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 2).Value2  '                       Save the Date as the OldestDate
                            RowToDelete = cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 2).Row    '                   Save the RowToDelete for the OldestDate
                        End If
                Else
                    Exit For                                                                '               Exit for loop
                End If
            Next
'
            Rows(RowToDelete).EntireRow.Delete                                              '       Delete the row with the earliest date that matches all criteria
'
            GoTo StartOfForEachLoop                                                         '       Go back to beginning of loop and check again
        End If
    Next
End Sub

 

[Coyotex3]

Ok, I tried to ask you for clarification and you didn't give me what I was trying to find out so try the following and see if it does what you want:

Sub DeleteRowPerTests()
'
    Dim OldestDate          As Date
    Dim PreviousRow         As Long
    Dim LastRowInColumnG    As Long
    Dim OffsetColumn        As Long
    Dim OffsetRow           As Long
    Dim RowToDelete         As Long
    Dim StartRowData        As Long
    Dim cell                As Range
'
    StartRowData = 6
'
    LastRowInColumnG = Range("G" & Rows.Count).End(xlUp).Row                                ' Get last row # used in column G
'
StartOfForEachLoop:
    For Each cell In Range("G" & StartRowData & ":G" & LastRowInColumnG)                    ' Loop through the G column
        OffsetColumn = 0
        OffsetRow = 0
'
        If cell.Value = "Cash Available" And cell.Offset(OffsetRow, OffsetColumn + 1) <> vbNullString And _
            cell.Offset(OffsetRow, OffsetColumn + 1) < cell.Offset(OffsetRow - 1, OffsetColumn + 1) And _
            cell.Offset(OffsetRow - 2, OffsetColumn - 3) = "Okinawa" _
            And cell.Offset(OffsetRow - 2, OffsetColumn - 6) = "OsaCon" Then                '   If all conditions are met then ...
'
            OldestDate = cell.Offset(OffsetRow - 2, OffsetColumn - 2).Value2                '       Save the OldestDate
            RowToDelete = cell.Offset(OffsetRow - 2, OffsetColumn - 2).Row                  '       Save the RowToDelete for the OldestDate
'
            For PreviousRow = 3 To RowToDelete - StartRowData
                If cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 3) = "Okinawa" And _
                    cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 6) = "OsaCon" Then  '           If both other conditions are met then ...
                        If cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 2).Value2 < OldestDate Then  '               If older date found then ...
                            OldestDate = cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 2).Value2  '                       Save the Date as the OldestDate
                            RowToDelete = cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 2).Row    '                   Save the RowToDelete for the OldestDate
                        End If
                Else
                    Exit For                                                                '               Exit for loop
                End If
            Next
'
            Rows(RowToDelete).EntireRow.Delete                                              '       Delete the row with the earliest date that matches all criteria
'
            GoTo StartOfForEachLoop                                                         '       Go back to beginning of loop and check again
        End If
    Next
End Sub

Sorry that I did not answer the question properly. Did you want me to upload the xl2bb file instead?


Not sure what is going on with my sheet, but this macro is not doing anything. It is going from the first If to the end of the sub.

 

[johnnyL]

Sorry, didn't realize you changed the name:

Sub DeleteRowPerTests()
'
    Dim OldestDate          As Date
    Dim PreviousRow         As Long
    Dim LastRowInColumnG    As Long
    Dim OffsetColumn        As Long
    Dim OffsetRow           As Long
    Dim RowToDelete         As Long
    Dim StartRowData        As Long
    Dim cell                As Range
'
    StartRowData = 6
'
    LastRowInColumnG = Range("G" & Rows.Count).End(xlUp).Row                                ' Get last row # used in column G
'
StartOfForEachLoop:
    For Each cell In Range("G" & StartRowData & ":G" & LastRowInColumnG)                    ' Loop through the G column
        OffsetColumn = 0
        OffsetRow = 0
'
        If cell.Value = "Cash Availables" And cell.Offset(OffsetRow, OffsetColumn + 1) <> vbNullString And _
            cell.Offset(OffsetRow, OffsetColumn + 1) < cell.Offset(OffsetRow - 1, OffsetColumn + 1) And _
            cell.Offset(OffsetRow - 2, OffsetColumn - 3) = "Okinawa" _
            And cell.Offset(OffsetRow - 2, OffsetColumn - 6) = "OsaCon" Then                '   If all conditions are met then ...
'
            OldestDate = cell.Offset(OffsetRow - 2, OffsetColumn - 2).Value2                '       Save the OldestDate
            RowToDelete = cell.Offset(OffsetRow - 2, OffsetColumn - 2).Row                  '       Save the RowToDelete for the OldestDate
'
            For PreviousRow = 3 To RowToDelete - StartRowData
                If cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 3) = "Okinawa" And _
                    cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 6) = "OsaCon" Then  '           If both other conditions are met then ...
                        If cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 2).Value2 < OldestDate Then  '               If older date found then ...
                            OldestDate = cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 2).Value2  '                       Save the Date as the OldestDate
                            RowToDelete = cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 2).Row    '                   Save the RowToDelete for the OldestDate
                        End If
                Else
                    Exit For                                                                '               Exit for loop
                End If
            Next
'
            Rows(RowToDelete).EntireRow.Delete                                              '       Delete the row with the earliest date that matches all criteria
'
            GoTo StartOfForEachLoop                                                         '       Go back to beginning of loop and check again
        End If
    Next
End Sub