excel vba code
Train Strikes - 7th, 8th & 9th May 2024
Hide
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA code

Excel VBA code

resolvedResolved · High Priority · Version 2010

Andrey has attended:
Excel Advanced - Formulas & Functions course
Excel VBA Introduction course

Excel VBA code

Hello,

I need help with Excel VBA code for the below (should be simple):

- If "Core Sector Level1" (Column O) = “Equity” and the "PriceChangePercent" (Column I) < 5, then highlight these rows in yellow.

Alternatively,

- If "Core Sector Level1" (Column O) = “Equity” and the "PriceChangePercent" (Column I) < 5, then remove/delete these rows from the file.

Thank you and Regards,

Andrey

RE: Excel VBA code

Hi Andrey,

Thank you for the forum question.

Please have a look at the code below.

In the line:
lRows = Range("A5").CurrentRegion.Rows.Count - 1

Change the reference A5 to a cell inside your table. I do not know from which row your table starts from. The line will find out have many rows you have in your table minus one. This information the For Next loop needs. If you your table has two header rows change it to minus two.

You will also need to change every time you see "5+lCounter" in the code. The code below asume that the first row you want to test is row 6. If your first row you want to test is row 10 change 5+lCounter to 9+lCounter.



Sub DeleteRows()
Dim lRows As Long
Dim lCounter As Long


lRows = Range("A5").CurrentRegion.Rows.Count - 1

For lCounter = 1 To lRows

If Cells(5 + lCounter, "o") = "Equity" And Cells(5 + lCounter, "I")>5 Then
Rows(5 + lCounter).EntireRow.Delete
End If
Next lCounter


End Sub


I hope the answer makes sense.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA code

Hey Jens,

Thank you for the quick response and the code!

The code seems to delete the other rows, though (that are > 5). We need to get these same rows (that are < 5) deleted, instead. Can you please advise?

Thank you again and Regards,

Andrey

RE: Excel VBA code

Hi Andrey,

Sorry it is my fault. I have changed > to <.

Sub DeleteRows()
Dim lRows As Long
Dim lCounter As Long


lRows = Range("A5").CurrentRegion.Rows.Count - 1

For lCounter = 1 To lRows

If Cells(5 + lCounter, "o") = "Equity" And Cells(5 + lCounter, "I")<5 Then
Rows(5 + lCounter).EntireRow.Delete
End If
Next lCounter

End Sub


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA code

Hey Jens,

Hope the weekend was good!

Unfortunately, not all rows under the criteria (Equity and Variance < 5) get deleted. Not sure what's wrong. Would it be easier if I sent you the file (no sensitive data in it)?

Thanks again and Regards,

Andrey

RE: Excel VBA code

Hi Andrey,

You can send the file to:

info@stl-training.co.uk




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA code

Hi Andrey,

I am unfortunately out of office the following days, but if you can send the file before 4:30 today I have a chance to look at it today<

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA code

Email sent! Thank you again for the help!!

Kind Regards,

Andrey

RE: Excel VBA code

Hi Andrey,

I have still not received the file.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA code

Hey Jens,

I've sent it to: STL (info@stl-training.co.uk). I'll resend it again.

Thanks again and Regards,

Andrey

RE: Excel VBA code

Hi Andrey,

Try:

Sub DeleteRows()
Dim lRows As Long
Dim lCounter As Long


lRows = Range("A1").CurrentRegion.Rows.Count - 1

For lCounter = lRows To 1 Step -1


If Cells(1 + lCounter, "o") = "Equity" And Cells(1 + lCounter, "I") < 5 Then
Rows(1 + lCounter).EntireRow.Delete
End If
Next lCounter

End Sub



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA code

Hey Jens,

Looks like your code was correct from the very beginning (and the issue was on my end)! It worked perfectly - thank you!!

Can I ask for more, though? :) If I want to apply a second criteria regarding the remaining rows, do I do this within the same sub? For example, if I want to add "Now remove all equity where Core Sector Level2 (column P) = ETFs".

Thank you again and Regards,

Andrey

RE: Excel VBA code

Hey Jens,

Please ignore - I was able to figure out the rest myself.

Thank you very much for your help here!!

Kind Regards,

Andrey

RE: Excel VBA code

Hey Jens,

Hope you are doing well!

I have a follow-up question: How can we code a 3-criteria requirement? For example,

If Cells(5 + lCounter, "AM") = "Equity" And Cells(5 + 1Counter, "AI") = "PL" And Cells(5 + lCounter, "V") < 10 Then
Rows(5 + lCounter).EntireRow.Delete

The above comes up as an error.

Thank you again and Regards,

Andrey

RE: Excel VBA code

Hi Andrey,

In the second test you have 1counter and not icounter.


kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA code

Silly me!! :)

Worked great! Thank you very much again, Jens! Much appreciated, indeed!

Kind Regards,

Andrey

RE: Excel VBA code

Hey Jens,

Another question - apologies!

I want to create a range that includes county codes, for example, Range "Country" = JP, MX, US, CL and BG. And I want a code as follows:

- If the Cell (in column AI) contains a county from that range, Then do not delete the row
Else Delete the row

Thank you again and Regards,

Andrey

RE: Excel VBA code

Hi Andrey,

Can I please ask you to raise it as a new forum question. It is a new question and I am not in the office until next Thursday. Then another trainer will answer you tomorrow.

Thank you.



kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA code

Will do - thanks again, Jens! Enjoy your time off and Happy Holidays!!

Kind Regards,

Andrey

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
excel consultants and other training courses.

Excel tip:

Formula for last day of month

In some cases it is necessary to find the last day of a month for a given date. If you use the following formula, you can achieve this, ie; if you have a column of dates, use this formula to find the end of month for each day by using the fill handle. The formula is as follows, and assumes in this example that the first date in question is in cell C5, in any other cell type; =DATE(YEAR(C5),MONTH(C5)+1,1)-1

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.06 secs.