hi, i have a lot columns of numbers ( A all the way to BZ), and i need to write a macro to locate the cell with maximum value in each column. And i cant use "worksheet.function" as it greatly slows down my code. Wondering if anyone has any suggestions? I tried googling and all i found is using the address, match, max function .Nothing macro related.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
cant find a code for locating maximum value in a column macro
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,224
- Office Version
- 365
- Platform
- Windows
I don't know that you can do it efficiently without using "Worksheet.Function". You could do it using loops, but loops are far less efficient than using a "Worksheet.Function".
See: https://stackoverflow.com/questions/5215616/vba-cell-address-of-max
Note that there are other ways to speed up your code, like disabling ScreenUpdating and Automatic Calculations until after your code completes, and making sure that you eliminate (or at least limit) your use of "Select" and "Activate" in your VBA code.
Also, limit your functions so that they are only running against the populated cells in a column, and not the whole entire column.
If you need help making your code more efficient, post it here.
See: https://stackoverflow.com/questions/5215616/vba-cell-address-of-max
Note that there are other ways to speed up your code, like disabling ScreenUpdating and Automatic Calculations until after your code completes, and making sure that you eliminate (or at least limit) your use of "Select" and "Activate" in your VBA code.
Also, limit your functions so that they are only running against the populated cells in a column, and not the whole entire column.
If you need help making your code more efficient, post it here.
Upvote
0
