Can This Code Be Modified To Run Faster
May 30, 2009way to bring more efficiency in this code.
I need to run this code on 100,000 rows every week and this takes hours to finish.
ADVERTISEMENT
Amend Code To Run Faster
Sep 5, 2008I have the following code kindly supplied to me by someone else and I'd like to know if anyone can suggest any ways to speed up the running of it. It currently takes about 4 minutes to run (there are about 5000 cells to loop through)
Basically it looks for a value in Col C - works out the number of rows to fill up by searching a range in Col D - then copies value in Col C up by that number of rows.
Make Code Faster/better Best Practices
Dec 27, 2008I have the following section of code and i am trying to make it faster. Currently when this procedure runs the screen "changes" durings its execution. Now i know i can "turn off screen updating" and this will increase speed but are there commands in the code which will prevent the screen from updating. I know activating or selecting things causes the screen to change. Also, any inputs to make this faster/better would be greatly appreciated.
View 14 Replies View RelatedFaster Calculation Than SumIf Code
Nov 21, 2006I have the following code performing SumIf and searching roughly 9,000 rows of data. Calculation takes roughly 15 seconds on the high side.
Bonus question from a rookie - This code runs well in Sheet2, but errors out on the SumIf's when placed in a module or userform. What needs to be changed for it to work outside of Sheet2?
Private Sub DistNeg_Click()
Dim BotRow As Long
Application.ScreenUpdating = False
Worksheets("Sheet2").Range("A4:E65536").ClearContents
'Copy current selected Distributor names to sheet2
Worksheets(1).Range("DIST1").Copy Destination:=Worksheets(2).Range("A4")
'Define the bottom row of distributors in this list.
BotRow = Worksheets(1).Range("DIST1").Rows.Count + 3
'Sum for PY, CY and $CHG sales. Create percentage Chg column.
Worksheets(2).Range(Cells(4, 2), Cells(BotRow, 2)).FormulaR1C1 = "=SUMIF(Sheet1!
Why Is My Code So SLOW, Anyway To Make It Faster
May 3, 2009I am only looping it through about 4 thousand records, looking for match and dups and moving it over to the other sheet, so why is my code running so slow?
I have run other programs where the records are in 40 thousand and it doesn't take as long as it is right now. Is there a way to make code run faster?
Optimizing Macro To Run Faster - Timer Code
May 3, 2007Here is exactly what was described:
A good place to start is to add some "timer code" in your macro. Add a small routine that saves a time value, and another routine that compares that saved value to the current time and displays the difference. At the beginning of a section of code you want to analyze, you call the first routine (which saves the start time), and then at the end of the section of code you call the second routine. In that way, you can determine which portions of your code are taking the longest time to execute. These are the code sections you then focus on, so you can figure out what they are doing that is taking so long.
This sounds like a great tool for optimization...
Modified A Save As Vba Code
Jan 29, 2006I'm trying to modified this line code. The 1st line code below opens a dialog box where you wan to save it.
strPictureFile = Application.GetSaveAsFilename("", "JPEG Files (*.jpeg), *.jpeg", , "Save as JPEG")
Now here I tried to modified it to just save it in a file folder as a jpeg. But its giving me a error. Am I missing something?
strPictureFile = Application.SaveAsFilename = "C:MyPictureD.jpeg"
Add Date Last Modified To Existing VBA Code
Feb 10, 2014I copied this snippet from the net and it has been working like a charm. I would like to add DateLastModified to it, but I can't get it to work, I'm still kind of new to more complex VBA codes. Here is the code...
Code:
Private Sub CommandButton1_Click()
Dim cell As Range
Dim selcell As Range
Dim Value As String
Dim Folder As Variant
Dim a As Long
ReDim Folders(0)
Set cell = Range("B8")
[Code] .........
VBA Code To Find Last Modified Document In A Folder
Dec 18, 2012I currently have a code that searches a folder, opens a spreadsheet, and then copies over the "Status" tab to the Master document.
New spreadsheets will be created a couple times a week, so I would like the code to be able to search this folder for the most recently modified document and then open THAT document.
So, if my folder has..
Report 10May11
Report 05May11
Report 17May11
Then, I would want a code to search in this folder for the most recently updated document (in this case "Report 17May11.xlsx) and then go through the step I have above where it copies over the various tab.
Basically, this code would I guess replace the example code I have below.
Sub WorkbookOpen()
....
End Sub
Code To Update Cell With Current Date And Time When Other Cells Are Modified
Mar 11, 2014I needed a code that would input the current date and time in the cells in column N whenever changes were made to any cells in the row from columns A to M. For example, if I change a name in cell 6D, then cell 6N would automatically change to the current date and time.
I found a useful code on a forum (maybe here, don't know for certain) and modified it to suit my needs (see below). I am however now getting a debugging error suggesting that the second line that reads "Private Sub Worksheet_Calculate()" is causing an error.
Creating A Customized Accumulative Code Week (modified WEEKNUM) Formula
Jul 4, 2008My company uses a customized accumulative code week system, as in the year 2000 to today is about 442 weeks with the year 2008 starting around week 416.
Here is how my formula works(or how I would like it to work), if the date (in cell F8) is blank, nothing is displayed, if there is a date (format 2008-07-03) and it falls within 2008, WEEKNUM+416(416 is a fixed reference in J4), if the date falls within 2009, WEEKNUM+416(J4)+52, if the date falls within 2010, WEEKNUM+416(J4)+104. I only need it to go from 2008 to 2010.
This way J4 is a fixed code week reference where 416 is added to each week number, but it does not work for years 2009 and 2010 as it does not add the weeks from the starting reference.
Here is a copy of my formulas:
=IF(AND(F8)=0,""*(IF(AND(F8)
Faster Than For Each
Apr 4, 2007I'm using the following code to populate the columns next to the dynamic named range dataforgraph (varies in length). It works, but the problem is that it takes ages. Is there a more efficient way of doing it. It is important that it only populates the cells next to the varying dataforgraph. The avg1 and stadev1 are the same for each entry (thus constants)
For Each c In Sheet3.Range("dataforgraph")
c.Offset(0, 1) = Sheet3.Range("avg1")
c.Offset(0, 2) = Sheet3.Range("avg1") + Sheet3.Range("stadev1")
c.Offset(0, 3) = Sheet3.Range("avg1") - Sheet3.Range("stadev1")
c.Offset(0, 4) = Sheet3.Range("avg1") + 2 * Sheet3.Range("stadev1")
c.Offset(0, 5) = Sheet3.Range("avg1") - 2 * Sheet3.Range("stadev1")
Next c
Calculates Faster Than IF
Aug 21, 2009I have an entire row of if statements that basically check other sheets if a number occurs. If it does then it will display "Pending Approval" ,"Open", or "Closed" depending on which sheet the # appears on .. Just wandering if their was a better formula for this..
View 7 Replies View RelatedFaster (non-loop?) Way Of Doing This
Jan 22, 2009Is there a faster (maybe a non-loop) way of doing this?
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Test1 As Single
Dim Test2 As Single
Dim Test3 As Single
With Sheet2
For Test1 = 33 To 52
If .Range("J" & Test1).Value = "End" Then
.Rows(Test1).Hidden = True
Else: .Rows(Test1).Hidden = False
End If
Next Test1
This causes a bit of a delay (7 seconds or so) when switching to the sheet, which is causing the user to think that excel has locked up.
Is There Anything Faster That 'select Case'
Sep 1, 2008Is there a way to count if there are 5 or more in a range without
using 'select case'
or not using the 'intcounter' bit bit of code?
Set Startcell = Sheets("meetingstodate").Cells(xlrow, 2)
Set Endcell = Sheets("meetingstodate").Cells(xlrow, 7)
Set rng = Range(Startcell, Endcell)
lastrow = Sheets("meetingstodate").Cells(xlrow, 2).End(xlDown).Row
For xlrow = 2 To lastrow
arr = Range(Startcell, Endcell).Value
For j = 1 To UBound(arr)
For k = 1 To UBound(arr, 2)
Select Case arr(j, k)
Case A
intcounter1 = 1
Case B .....................
VBA Running Faster Than Excel
Nov 6, 2009I have discovered a strange problem with my VBA code. After testing it on several machines, I've found no problems with my workbook until now. After being run on a rather slow Windows Vista laptop, I've found that VBA appears to be running faster than Excel, and consequently it is creating errors as VBA asks Excel to do things before it has finished a previous action. This is causing macros to fail several times in each run, though can be solved simply by pressing 'debug' and then F5 to continue the code. Is there a way to get VBA to run slower to solve this? As I can't think of any other way of doing it? I've certainly not seen it happen on any XP or Win 7 computer!
View 9 Replies View RelatedFaster Way To Delete Rows
Nov 11, 2009My spreadsheet is a contract file that includes a list of part numbers being sold. Each part number refers to a lookup table of ALL about 20,000 valid part numbers and prices. Once the contract is finalized, I want to make the contract sheet smaller by deleting all rows in the lookup table that are not required for this contract.
The following code works, but it takes more than 10 minutes to work through the complete list. I read down the lookup table. For each record in the lookup table, I call a routine that reads through an array of the part numbers that are included in this contract. If the lookup table part number IS included in the contract, I skip it. If it is NOT required, I delete it. I then return to the main lookup table and read in the next lookup table record.
This is the main routine where I progress down the big lookup table.
'Work down the Price File range from top to bottom
Set RefTableRange = DSWPrices.Range("DSWPriceRange")
RefTableIndex = 1
Application.Calculation = xlCalculationManual
While RefTableIndex < RefTableRange.Rows.Count
RefTableIndex = RefTableIndex + 1
'check if this part number is included in the contract
Call CheckRefTableRow(RefTableRange, RefTableIndex)
Wend.....................................
Faster Way Of Doing VLOOKUPs On Multiple Items?
Nov 9, 2009Instead of doing a long long formula that is =VLOOKUP(x1,....)+VLOOKUP(x2,.....) is there a faster way of doing it in the one formula?
View 7 Replies View RelatedFaster Way To Loop For Price Indexing
Dec 10, 2009I'm trying to improve a code that Indexes a series of prices to a day (the first day of a range). I'm doing this to numerous named ranges. The code that i build takes ages to do this:
View 9 Replies View RelatedHow To Adjust Width Of Columns Faster
Nov 17, 2012 1. Is there a faster way to adjust the size of the columns? I had to use my mouse to stretch the column out. This can be quiet time consuming if there are 8 columns needing to adjust to the same size. ie Col A, B,I, J needs to be the same size.
2. Can Excel determine the best size for the column? Say I need a column with data saying "yes" and "no" and a column for the letter "X" only or do I have to stretch the width of the column myself?
VBA - Faster Delete All Visible Cells?
Jul 23, 2013Any way that the following code could perform faster? Unfortunately, i'm deleting 35,000 rows of data quite frequently from a filtered list. Could be up to 35 columns wide.
Code:
ls = Cells(Rows.Count, 1).End(xlUp).Row
If ls > 1 Then
Range("A2:A" & ls).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End If
Find Page Breaks Faster
Jul 21, 2007I need to find row numbers of the horizontal page breaks on a sheet quickly. The only method I have found to do this is by using the HPageBreaks property, which is painfully slow.
Is there another way of doing this that's faster? Or some other manner of using HPageBreaks that improves its speed?
The scenario is that I have a macro which generates a list of items where each item is two rows long. If an item intersects a page break--one row is on one page and the 2nd row is on the next page--I want to insert a row so that the entire item is on the next page.
Macro Run Faster In 2000 Than 2003
Feb 18, 2008I have a macro which is able to run very fast in Excel 2000. Almost 5s. When i run it in Excel 2003, it takes almost 4 to 5 mins to complete. Is there any patch in 2003 i need to run the macro smoothly?
View 4 Replies View RelatedOverflow Error - Faster Way Of Copying Through Macro
Nov 18, 2009I am transferring 10000 rows from 1 workbook to another..
It takes place smoothly for the first 30000 rows, but after that on the 'select' line it gives me an overflow error.
The code that I am using is as following: .....
Load Multiple CSV Files In A Spreadsheet Faster
Jan 5, 2010In my spreadsheet I load 26 CSV files. Each CSV file has 3 columns and 7 rows. It uses the connection query in Excel. It is only relatively slow to refresh the data. To refresh the data, I can press the refresh data button in Excel.
But in practice I use this code every 5 seconds:
Faster Copy And Paste Operations In A Macro
Aug 8, 2007To speed up copy and paste operations in a macro, I've heard that one can replace the following:
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").pasteSpecial
with
Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value
The second code should run faster as the clipboard is not used to store the copied info.
When I tried this, though, I didn't notice any improvement in macro running speed.
Match Criteria Then Replace Data - Faster
Jun 23, 2006Is it possible to take a list of numbers then match those numbers to another list of numbers as a whole? I'm trying to make changes to my code and take some of the advice from previous threads, but I can't visualize it. I'm trying to get away from matching each list of numbers to each other line by line. I have an existing sheet (Master) that has several col's of data. Then I have another worksheet that has updated info that I need combined into the master. What I've done so far is added the information to the bottom of my existing data and sorted.
Now I have setup an advanced filter to display the dupes. Here is where it gets tricky..I can't just delete dupes b/c there are cols to the right that have history that cannot be deleted. So I need to copy ranges A to J of the new data and replace the old data within that range.
Making File Smaller With Faster Load Times
Mar 4, 2014Is there a way to reduce a file's size to make it smaller with faster load times? I have a file that has ballooned in size due to the number of formulas and images I have installed.
Sometimes the file shuts down on it's own and make's my computer really slow.
Does SSD Hard Disk Or A Faster Chip Improve Excel Performance
Apr 26, 2013If I want to improve my excel performance like ability to open multiple excel and sheets to perform calculation, should I change to a SSD or a faster intel chip? My current PC sometimes give me a black screen for my excel sheet or is unable to save certain sheets when I open too many sheets.
View 3 Replies View RelatedModified By According To Each Cell
Jan 12, 2012I wonder if is posible to discover following:
Who modified the cell from various users.
After that how many cells from xls fiile were modified by one user and how many by another user.