optimize

  1. Z

    Choosing top scoring row (with complex logic/rules)

    I have a set of data with different groups ("colors"). For each group (color), there are questions that apply to "categories" ("A", "B", "C", etc.). Each has a numerical score. I need to pick the highest scoring question for each category, for each group (color). Example: Group "Blue" has 2...
  2. A

    How to optimize Index Match , for large sets of data ?

    Hello I have formula that's working fine in small data but now I have to use in a large data set so I really need a way to optimize the formula. the table is like this, <tbody> Ref Name Height Strength Weight 1 A 150 120 60 2 B 150 120 60 3 C 110 10 60 4 D 180 200 80 </tbody>...
  3. V

    Hardware for Optimizing Calculations

    Hello, I was hoping the community might be able to help me figure out what tech specs in a new computer could help me optimize the performance for an Excel file I use to produce a twice/weekly report/newsletter as a side business. The file is ~100 Mbs and growing. The file essentially has 3...
  4. O

    Optimize code

    Would it be possible to optimize the code below? Sub Graphs()Dim lr As Integer, lc As Integer, r As Integer Sheets("data_pivot").Visible = True lr = data.Cells(Rows.Count, 1).End(xlUp).Row lc = data.Cells(1, Columns.Count).End(xlToLeft).Column lr2 = data_pivot.Cells(Rows.Count...
  5. N

    Sumproduct needs optimization

    Hi, I am using SUMPRODUCT to summarize the vacation days for all employees for each month. My formula looks like this: =SUMPRODUCT( ('Cal'!$A$1:$A$200=Summary!$A1)* (('Cal'!$D$4:$ND$160="VC")+ ('Cal'!$D$4:$ND$160="SK")+ ('Cal'!$D$4:$ND$160="TR")+ ('Cal'!$D$4:$ND$160="JD")+...
  6. D

    How to optimize VBA code with loop

    Hi, My below code is working fine but I want to optimize it using a loop. Can I re-write below code using any loop in VBA? Set rng2 = ws.Range("O14:O500") Set rng3 = ws.Range("P14:P500") Set rng4 = ws.Range("Q14:Q500") Set rng5 = ws.Range("R14:R500")...
  7. C

    3D INDIRECT, INDEX, OFFSET as array function?

    First post; I searched the forum and found threads that are pretty close but not quite right for what I'm trying to do. I have a working function that is unnecessarily redundant, and I'm trying to convert it to an array function. Here is the raw: = SUM( IF(NOT(ISERROR(OFFSET(INDEX(INDIRECT("'"...
  8. N

    Long processing times...Named ranges with dynamic data?

    I have a workbook with lots of formulas and sheets. It is updated weekly and the data grows irregularly with every update. As of right now I have my formulas referencing entire columns in order to capture all new data. This is starting to catch up with me, as the processing time is detracting...
  9. B

    Help Optimizing Copy Paste Rows Between Sheets Code

    The below code works, but it takes about 5 minutes to complete. I was hoping someone might be able to share some tips on how to get this to run much faster? I really appreciate any help or advice. Thank you! While Len(Range("A" & CStr(LSearchRow)).Value) > 0 'If value in column Q...
  10. Y

    What can I do to enhance the speed of this macro?

    Hi, I have a spreadsheet that has a list of URLs on Sheet2. The macro pulls the JSON data from each URL and then pastes them in a certain order on another sheet. Since the data pull is huge, it keeps freezing when i try to do all 196 URLs at once. But if i do them 50 at a time, it works just...
  11. N

    VBA Optimization

    Hi All, Hoping someone can point me in the right direction with regards to optimizing some vba. The below code takes 12 seconds to run against 3000 cells selected in each worksheet, it looks at one column in each worksheet, looks for a duplicate value and highlights the cell accordingly, the...
  12. J

    HELP with formulas in VBA code - what's the most efficient way?

    Hey guys, here's a part of my code that contains putting these formulas in 7 different columns for i amount of rows (i = ~210,000). At the end of the code, I copy the range and paste as values to get rid of the formulas, but keep the values. It works, but the problem is that it REALLY slows...
  13. C

    Macro Running Out Of Memory When Run Twice

    Hi everyone, I am new to this forum but have been reading a large number of posts recently as I am currently self teaching VBA for use at work! I currently am having an issue with a bit of code that I have created. The aim of the code is to autofilter multiple sheets depending on a cell value...
  14. S

    Solver Macro VBA Loop Calculation Extremely Slow

    Hi, I am using Solver to iteratively find best fit solutions for rather complex polynomial equations. I’ve created 3 macros by recording VBA code for loop calculation of desired “variable”. I am only a beginner and don’t have time or need to become proficient. I have 8760 rows (8760 hrs/year)...
  15. P

    Code Optimization on Large Sheets

    I have a Spreadsheet with over one-million rows of data. Of course it takes awhile to load or even update. Plus when I try to run a filter it vegetates every time. I was wondering if there are things I can do (e.g. save in older formats, remove "bells and whistles" or formatting) that would...
  16. D

    Cut and Paste Optimize

    Hi, Is there a way to do this cut and paste without using the recorder's Select? range("Q3").Select Selection.Cut range("P3").Select ActiveSheet.Paste Daniel
  17. L

    Optimize/ Simplify Macro

    Sub Import() Application.ScreenUpdating = False 'Import basic stuff 'Network Sheet8.Range("U2:U3001") = Sheet3.Range("AB2:AB3001").Value 'Network Addon Sheet2.Range("I5:I3004") = Sheet8.Range("V2:V3001").Value 'FAN Sheet2.Range("B5:B3004") = Sheet3.Range("A2:A3001").Value 'BAN...
  18. B

    Optimize list for payback <X

    I'm a newbee... that needs help to solve what is probably a simple solution. From the project list (below) I need to select those projects that will collectively optimize a payback of <2. Save Cost Payback $6,529.54 $16,273.66 2.492315534 $7,498.08 $11,419.03...
  19. L

    Slow macro, usaully crashes my PC. Need help optimizing it

    So I have created this MACRO for work, to ensure everything is getting charged right. Well I would like it to run through pretty much the entire database data for 2 years, I can not get it to last that long. I can copy the data to a separate spreadsheet and run the macro in chunks, but I would...
  20. J

    VBA Project - Asset Labels - Code structure and optimization assistance, please :)

    (Spoiler, long post) Hello all, I am relatively uneducated when it comes to VBA, but I have muddled my way into a semi-functioning project that until recently worked fine. Let me begin with a little background. I work in Security and we moved to a different Asset Tracking system a little bit...

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top