Finding the same value from a variable and selecting all these rows within a range

[ija34]

Hey so I'm looking for a generic code. Basically, I need it to find the value in a column using a calculation already named on the code so basically it would be something like .Value = Calc. I need it to find all cells in a column with this value (should only be between 1 and 20 cells in total) and then I need it to select the cells and delete them and shift up. Cheers.

I've managed to get it to delete one row but not all of them, the code I used is

Please Login or Register  to view this content.

[torachan]

have you tried looping from the bottom upwards (step -1)

[ija34]

have you tried looping from the bottom upwards (step -1)

Cheers for your reply. No luck though. It doesn't delete any rows. I'll leave code incase I've written it wrong.

Please Login or Register  to view this content.

I just used delete to test first and see if cell is deleted. I'll add range once it's deleted multiple values from the column.

[torachan]

Please Login or Register  to view this content.

This does not make sense - what is it finding.

put the word Stop in the code immediately after the above code and see what the value of nextTrade is.

[beyond Excel]

Hi. I'll show you one way to do it:

Please Login or Register  to view this content.

Of course you will replace Select with Delete.

[nimrod1313]

This does not make sense - what is it finding.

put the word Stop in the code immediately after the above code and see what the value of nextTrade is.

Hi Torachan:
I did ... and it makes sense to me ... it provides the "nextTrade" number from looking at the incremented trade numbers in the list .
(IE. Get the last trade number in list and increment it by 1 )

... I kind of like it ... think I will use this in the future .
I also like the name Ija gave the variable as it clearly indicates the "intent" of the variable as it is literally the "Next Trade" in the list.
So many of us give variables names that don't follow the practice of variable names that provide intent. I know i'm guilty of it on occasion .
(boy will I feel foolish if that's not what Ija was doing LOL)

ScrShot16.gif

ScrShot17.gif

Please Login or Register  to view this content.

[ija34]

Hi ToraChan:
I did ... and it makes sense to me ... it provides the "nextTrade" number from looking at the incremented trade numbers in the list .
(IE. Get the last trade number in list and increment it by 1 )

... I kind of like it ... think I will use this in the future .
I also like the name Ija gave the variable as it clearly indicates the "intent" of the variable as it is literally the "Next Trade" in the list.
So many of us give variables names don't follow the practice of variable names that provide intent. I know i'm guilty of it on occasion .
(boy will I feel foolish if that's not what Ija was doing LOL)

Attachment 792822

Attachment 792823

Please Login or Register  to view this content.

Thanks nimrod. That's exactly what it is for and yeah I like to try and keep it simple especially since the full vba code for this button is lengthy so I need to be able to clearly see what each section of code is doing.

[nimrod1313]

Thanks nimrod. That's exactly what it is for

Hi Ija34
Well I like it !
Off the top of my head I can't think of a cleaner/clearer way to accomplish this .

Though , from a "style" standpoint I would have done it this way ..

Please Login or Register  to view this content.

[nimrod1313]

since the full vba code for this button is lengthy so I need to be able to clearly see what each section of code is doing

Hi Ija
Well if you ever want some "peer review" on cleaning up code I would be interested .

[ija34]

Hi. I'll show you one way to do it:

Please Login or Register  to view this content.

Of course you will replace Select with Delete.

Hi Beyond Excel. This works perfectly, thank you. Massive help.

[nimrod1313]

Hello Ija:

Your original code was very close.
If you're going to delete row by row you need to start from bottom and work up the sheet.
(you'll notice torachan mentioned this in their first post)

Reason :
If you start from top you will miss rows .
For example let's say you loop from top and have just deleted row 4 before going onto row 5.
Well if you delete row 4 then row 5 becomes row 4 , but since you've already done row 4 the new row 4 is not evaluated.
This issue goes away if you delete from bottom.
NOTE : There are better ways to delete rows ... but I think you should know this process


Starting from bottom row and working up (demo , not tested)

Please Login or Register  to view this content.

[ija34]

Hello Ija:

Your original code was very close.
If you're going to delete row by row you need to start from bottom and work up the sheet.
(you'll notice torachan mentioned this in their first post)

Reason :
If you start from top you will miss rows .
For example let's say you loop from top and have just deleted row 4 before going onto row 5.
Well if you delete row 4 then row 5 becomes row 4 , but since you've already done row 4 the new row 4 is not evaluated.
This issue goes away if you delete from bottom.
NOTE : There are better ways to delete rows ... but I think you should know this process


Starting from bottom row and working up (demo , not tested)

Please Login or Register  to view this content.

Ahhhh I see, that's where it went wrong. I tried this formula earlier but I put for i = 4 to lr step -1 so obviously I should have been going from last row to 4.

[nimrod1313]

Ahhhh I see, that's where it went wrong. I tried this formula earlier but I put for i = 4 to lr step -1 so obviously I should have been going from last row to 4.

Yes.
But let's be clear, updating a sheet row by row (eg. deleting , inserting , clearing) has it's pitfalls.
Primarily there is excel overhead to each change. This overhead takes time for each event/change. (ie. this approach is not "scalable" )
There are many approaches to diminish this affect , these approaches fall primarily into two strategies.
1) Update many rows/ranges at one time vs single row/range at a time
2) turn off some of the applications overhead generating events during the running of the macro


Lessen Application Overhead during macro run

Please Login or Register  to view this content.

WARNING : If your code fails/stops before getting to the lines that re-activate the application events then they will remain off.
(ie. Have all your errors in code workout prior to this OR have some good error handling code in place.

[ija34]

Yes.
But let's be clear, updating a sheet row by row (eg. deleting , inserting , clearing) has it's pitfalls.
Primarily there is excel overhead to each change. This overhead takes time for each event/change. (ie. this approach is not "scalable" )
There are many approaches to diminish this affect , these approaches fall primarily into two strategies.
1) Update many rows/ranges at one time vs single row/range at a time
2) turn off some of the applications overhead generating events during the running of the macro


Lessen Application Overhead during macro run

Please Login or Register  to view this content.

WARNING : If your code fails/stops before getting to the lines that re-activate the application events then they will remain off.
(ie. Have all your errors in code workout prior to this OR have some good error handling code in place.

So what does each of this bit of code do? My goal is to have a fully automated workbook that send info between accounts and for trades. I only need to enter event info then the rest of it is automated then at the end I'll have a full statement list of trades and an analyser that will calculate P & L and have graphs for profit over time. So the only manual things I need to do is data entry and the set up at the beginning. At the moment I'm trying to get each section working but since I'm not an expert at vba and this is my first time, I don't know how to write to make it run faster and efficiently so I'm just aiming to get it working then see if you guys can help me rewrite the code in a more efficient way.

[nimrod1313]

I'm just aiming to get it working then see if you guys can help me rewrite the code in a more efficient way.

Cool , good approach.
I just trying to keep you up enough in understanding so that you could make informed decisions.
Just having you understand that there are ways to make faster is enough for now , since you will revisit later.

Please Login or Register  to view this content.

Explaination:
Each update of a cell, range , row excel recalculates all formulas and refreshes the screen.
This all takes time... so many people turn these things off while looping and changing alot of cells, range, rows
and then turn it back on after loop.

... but your right ... worry about this after you get everything working