VBA for Deleting entire rows if cell contains certain text or value

[TheresavG]

Please help

I need a vba in excel to the following please:
1 If Col C contains date starting with 2022-06-28 then keep it, delete the rest entire rows
2 Then if Col J Contains text " Declined" - Then delete whole row
3 Then if Col J Contains text " Refund" - Then the amount in Col G must get a Minus in front

So first I want to only keep transactions for a certain date starting with example: 2022-06-28
Then from those I want to delete all the declined lines in col J
and then I want to put a minus in front of Amounts In Col G if Col J shows " Declined"

I tried the XL2BB way it doesnt paste my mini sheet here, I also tried a jpeg but I cannot get it small enough to upload



Thank you so much

Theresa

 

[lexusap]

Hi Theresa,

This is the code I would you

Sub RunMacro()
Dim LastRow As Long, X as long
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

For X = LastRow to 2 step -1  'This is assuming your start data at row 2 
    If cells(X, "J").value = " Declined" then
          cells(X, "J").entirerow.delete
    elseif cells(X, "J").value = " Refund" then
          cells(X, "G").value = -1 * cells(C, "G").value
    elseif cells(X, "C").value = "2022-06-28" then
          cells(X, "C").entirerow.delete
    end if

Next X

End Sub

 

[TheresavG]

Hi Lexusap
Thanks for the fast reply
I copied it to my excel, changed the 2 to 4, cause the vba should only consider from col A4 (the rest will be headings)
But it doesnt seem to run, just does nothing,
Will still play around and see what happens
I just also want to know for the date the VBA must be setup so if the cell CONTAINS / STARTS WITH date as 2022-06-28 it should be kept, rest of the dates should be deleted (entire rows)
Do you have a mail then I can send you my sheet?
Im struggling to upload a picture?
Thanks for the help
Theresa

 

[lexusap]

I am assuming issue with LastRow . use this LastRow instead of the first one, assuming that you have no data on column A ?

LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

 

[TheresavG]

Hi
I just copied it directly from Excel so you can have an idea of the data and cells
A1 is Exported data
A2 Showing
A3 Merchant
The data on which this VBA should run starts at A4

Exported data

Showing the last 1000 receipt(s) for Merchant 07867

Merchant

Terminal

ReceiptDateTime

BatchNumber

SequenceNumber

ReceiptNumber

TotalAmount(R)

Card

UTI

Type

AuthCode

InvoiceNumber

Action

VF

VF (00118253)

2022-06-29 09:28:05

9

9

5055

557.99

Visa Card

UTI0009

Sale

A00009

View

VF

VF (00118253)

2022-06-29 09:19:56

10

10

9467

1224.55

Debit Mastercar

UTI0010

Sale

A00010

View

VF

VF (00118253)

2022-06-29 01:27:57

16

16

3280

2500.00

Visa Credit

UTI0016

Sale

A00016

View

VF

VF (00118253)

2022-06-28 23:33:31

17

17

3279

281.70

Debit Mastercar

UTI0017

Sale

A00017

View

VF

VF (00118253)

2022-06-28 21:34:36

19

19

3277

3000.00

MASTERCARD

UTI0019

Refund

A00019

View

VF

VF (00118253)

2022-06-28 18:55:34

23

23

5044

1500.00

MASTERCARD

UTI0023

Refund

A00023

View

VF

VF (00118253)

2022-06-28 18:55:34

23

23

5044

1500.00

MASTERCARD

UTI0023

Declined

A00023

View

VF

VF (00118253)

2022-06-28 18:55:34

23

23

5044

1500.00

MASTERCARD

UTI0023

Refund

A00023

View

VF

VF (00118253)

2022-06-26 18:55:35

23

23

5044

1500.00

MASTERCARD

UTI0024

Sale

A00024

View

VF

VF (00118253)

2022-06-2918:55:36

23

23

5044

1500.00

MASTERCARD

UTI0025

Refund

A00025

View

VF

VF (00118253)

2022-06-28 18:55:37

23

23

5044

1500.00

MASTERCARD

UTI0026

Declined

A00026

View

VF

VF (00118253)

2022-06-28 18:55:38

23

23

5044

1500.00

MASTERCARD

UTI0027

Refund

A00027

View

VF

VF (00118253)

2022-06-28 18:55:39

23

23

5044

1500.00

MASTERCARD

UTI0028

Sale

A00028

View

VF

VF (00118253)

2022-06-26 18:55:40

23

23

5044

1500.00

MASTERCARD

UTI0029

Declined

A00029

View

VF

VF (00118253)

2022-06-28 18:55:41

23

23

5044

1500.00

MASTERCARD

UTI0030

Declined

A00030

View

VF

VF (00118253)

2022-06-26 18:55:42

23

23

5044

1500.00

MASTERCARD

UTI0031

Refund

A00031

View

VF

VF (00118253)

2022-06-26 18:55:43

23

23

5044

1500.00

MASTERCARD

UTI0032

Declined

A00032

View

VF

VF (00118253)

2022-06-26 18:55:44

23

23

5044

1500.00

MASTERCARD

UTI0033

Sale

A00033

View

VF

VF (00118253)

2022-06-26 18:55:45

23

23

5044

1500.00

MASTERCARD

UTI0034

Declined

A00034

View

VF

VF (00118253)

2022-06-26 18:55:46

23

23

5044

1500.00

MASTERCARD

UTI0035

Refund

A00035

View

VF

VF (00118253)

2022-06-26 18:55:47

23

23

5044

1500.00

MASTERCARD

UTI0036

Declined

A00036

View

VF

VF (00118253)

2022-06-26 18:55:48

23

23

5044

1500.00

MASTERCARD

UTI0037

Sale

A00037

View

VF

VF (00118253)

2022-06-26 18:55:49

23

23

5044

1500.00

MASTERCARD

UTI0038

Refund

A00038

View

VF

VF (00118253)

2022-06-26 18:55:50

23

23

5044

1500.00

MASTERCARD

UTI0039

Declined

A00039

View

 

[lexusap]

Try this code !

Sub RunMacro()
Dim LastRow As Long, X as long
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

For X = LastRow to 4 step -1  'Data start at row 4
    If cells(X, "J").value = "Declined" then     'I remove leading space from " Declined" to "Declined"
          cells(X, "J").entirerow.delete
    elseif cells(X, "J").value = "Refund" then     'I remove leading space from " Refund" to "Refund"
          cells(X, "G").value = -1 * cells(X, "G").value
    elseif Left(cells(X, "C"),10 ) <> "2022-06-28" then
          cells(X, "C").entirerow.delete
    end if

Next X

End Sub

 

[TheresavG]

Thank you once again, I really appreciate the help

It now almost works

Only problem still have is it doesnt delete all other dates exept for 2022-06-28
Still see transactions with dates 2022-06-26 etch after I run the VBA

 

[lexusap]

Replace the date formula with this and see whether it works or not.

elseif Trim(Left(cells(X, "C").Text, 10)) <> "2022-06-28" then

 

[TheresavG]

Hi, Thank you so much,
It works now,
I just placed the Date formula before the Refund formula

Thanks again

 

[TheresavG]

Sorry I just have one more question please

how would I write this formula if I want to keep dates for instance between lets say: 2022-06-24 until 2022-06-30 - rest to delete entire rows

elseif Trim(Left(cells(X, "C").Text, 10)) <> "2022-06-28" then

Thanks