optimize

Hi everybody. I'm trying to make my workbook lighter to calculate, but I have a question. Is lighter to use a named formula a few times in the same row, or should I use helper columns and compare the result in the other cells? For example 1: Using named formula in all cells "XYZ" that it's...

This full code pulls data from a work website into Excel. Can be up to 55000 rows. Currently on Excel 2019 it takes forever to run. Also uses between 33%-50% of cpu. On 2016 it worked fine. I use a code already that turns off all things like calculations, events, etc. I am thinking an array...

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...

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>...

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...

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...

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")+...

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")...

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("'"...

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...

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...

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...

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...

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...

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...

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)...

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...

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

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...

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...