Speed-up Slow Macro Code: Entries In Column 73 - 85 Will Be Copied From WorkbookRust To The Other Workbook If The Numer In Column 5 Is The Same
Mar 25, 2008
my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.
Sub Allmacros()
Dim WorkbookRust As String
WorkbookRust = ActiveWorkbook.Name
ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch"
Workbooks.Open Filename:= _
"C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls"
Sheets("Main_Overview").Select
Windows(WorkbookRust).Activate
Application.run ActiveWorkbook.Name & "!UpdateEntries"
Application.run ActiveWorkbook.Name & "!FilterMain"
'not ask to overwrite existing file
Application.DisplayAlerts = False
Workbooks("CH_Revenue_2008.xls").Save
Workbooks("CH_Revenue_2008.xls").Close
End Sub
Sub UpdateEntries()......................
View 7 Replies
ADVERTISEMENT
Mar 18, 2008
I have the following function in a worksheet module:
Private Function NixEmptyColumnsAndSuperfluousRows()
Me. Range("A1:A7").EntireRow.ClearContents
Me.Range("A1:A7").EntireRow.Delete Shift:=xlUp
Me.Range("BE1").EntireColumn.ClearContents
Me.Range("BE1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("AT1").EntireColumn.ClearContents
Me.Range("AT1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("AK1").EntireColumn.ClearContents
Me.Range("AK1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("Y1").EntireColumn.ClearContents
Me.Range("Y1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("O1").EntireColumn.ClearContents
Me.Range("O1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("K1").EntireColumn.ClearContents
Me.Range("K1").EntireColumn.Delete Shift:=xlToLeft..............
View 9 Replies
View Related
Apr 16, 2009
I have a workbook with many lookups, sumproducts, dynamic named ranges and cse formulas. How do I determine what is causing my workbook to be slow? Are there more efficient formula types that I can use?
Sample Formulas: ...
View 9 Replies
View Related
Sep 24, 2007
i am currently using the following code to copy records from one sheet to new sheets that that are created and named in the first part of the if(). this works fine, however when i am dealing with 50,000 records it still takes 5-10min to get them all sorted. I think this although functional is horribly inefficiant, and am wondering how i might be able to speed up the process. possibly rather than check each record, then copy then paste individually to sort them, then read and select the list of cells until value changes, then copy over at once. i dont know if this would be faster or not, let me know what you think.
Sub autorec ()
Dim wSheet
Dim newSheetName As Variant Dim FNAC As Double
Dim OU As Double
Dim DS As Double
Dim CCY As String
View 9 Replies
View Related
Oct 30, 2009
This used to (and on some machines still does) work very well, but all of the sudden on my work machine, it is incredably SLOW!! The whole program takes a list of tasks and dates, and creates a wall calendar from them. Then (the offending part, below in code) looks at each cell on the wall calendar and formats it by sizing the height of a merged cell so that the entire task is displayed, then goes on to put a square around it (works fine).
The formating of the cell height/width can take up to 11 SECONDS each cell. What is happening to do this? Again, it USED to, and on some machines still does, FLY through this process. But now it just crawls. Code below, with the 3 offending lines identified.
Private Sub Fit_Height(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
Application. ScreenUpdating = False
With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
An additional item (clue?) is that, after the macro is done running, it is still unbearably slow to do anything on-screen. Grabbing the column header and changing the column width takes 5 seconds or so before it snaps to the desired width and displays correctly. Closing Excel and reopening it takes care of that problem.
More advancement (as I wait). It seems to be the Application.PageSetup that kills it. As soon as I hit the first Application.PageSetup line, the app grinds to a halt, and almost every line after that that manipulates the screen format or the page format takes 5 or so seconds EACH to process. I put them all at the end, but that really doesn't solve the problem, nor explain it, nor give me a true solution.
View 4 Replies
View Related
May 7, 2014
What I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.
Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).
the function I'm working with right now is
=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)
View 13 Replies
View Related
Apr 4, 2007
I use the Application.ScreenUpdating = False all the time. Any there any other things like this which speed up macros?
View 9 Replies
View Related
Mar 6, 2010
I have written a macro which is sucessful in the sense that it does what I need. However, I ran it against my data for the first time today and it took forever. To give you an idea of the data size... my code had to loop through about 10,000 rows and move the ones that met the IF criteria to another sheet.
Can anyone provide any suggestions as to what might make my code faster? Should I take a different approach? I am definitly still wet behind the ears.
My code is below...
Dim count As Integer
count = 3
PeCount = 3
ActiveWorkbook.Worksheets("MyWorksheet").Activate
Do
If Trim(Sheets("MyWorksheet").Cells(count, 12).Value) = "1111111" Or _
Trim(Sheets("MyWorksheet").Cells(count, 12).Value) = "2222222" Or _
Trim(Sheets("MyWorksheet").Cells(count, 12).Value) = "3333333" Then
Sheets("MyWorksheet").Rows(count).Select
Selection.Cut
Sheets("YourWorksheet").Activate
Sheets("YourWorksheet").Rows(PeCount).Select
Selection.Insert Shift:=xlDown
Sheets("MyWorksheet").Activate
Sheets("MyWorksheet").Rows(count).Select
Selection.Delete Shift:=xlUp
PeCount = PeCount + 1
Else
count = count + 1
End If
Loop Until IsEmpty(Sheets("MyWorksheet").Cells(count, 2).Value)
End Sub
View 9 Replies
View Related
Apr 4, 2007
I am trying to determine in code whether a file exists in a certain location. This seems to be very slow even in a folder which contains just one file. Is there any way to speed up this process? Three seconds seems like a long time especially given that I will have to loop through this Sub many times. The files I am looking for are CSV so I can't use msoFileTypeExcelWorkbooks unless I can modify what file extensions this looks for. I only started dabbling with VBA a few weeks ago so it is entirely possible I'm barking up the wrong tree and should be using another method to acheive my aims.
Sub CISORTEST()
With Application.FileSearch
.NewSearch
.LookIn = "C:TEMP"
.SearchSubFolders = False
.Filename = "MYCSVFILENAME"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute > 0 Then
Call DoStuff()
End If
End With
End Sub...........................
View 6 Replies
View Related
Jul 8, 2014
The code I currently have allows me to search for one text entry in the column and then copy and paste all those entries into the next worksheet. There are 5 different text entries I'd like it to look for but can't figure out how to do it. I have a work around by using wildcards, but then I have to write another macro to delete out the ones I don't want.
Where is says "DNA - weapons", I'd also like it to allow for "DNA - paternity" and a couple other options.
Here is my current code:
Sub Copy_To_Another_Sheet_1()
Dim FirstAddress As String
Dim myArr As Variant
Dim Rng As Range
Dim Rcount As Long
Dim I As Long
Dim LastCol As String
Application.ScreenUpdating = False
[Code] ....
View 2 Replies
View Related
Aug 30, 2006
Im setting up a spreadsheet that does engineering calculations. Im using macros to run sizes from a standard schedule. It basically takes the values from one sheet (schedule) to another (calculation), then the result from the calculation sheet (Value only, not the link) is pasted back into the schedule. The macro seems very bulky and im sure that it can be made more efficient with a loop. here is a sample of the code from the macro;
Sheets("Calc sheet").Select
Range("C6").Select
ActiveCell.FormulaR1C1 = "=Schedule!R[1]C"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=Schedule!RC[1]"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Schedule!R[-1]C[2]"
Range("C9").Select
Sheets("Schedule").Select
Range("G7").Select..................
View 3 Replies
View Related
Sep 13, 2006
I am trying to parse and remove unwanted rows from a very large text file using At the moment, the application runs rather slow and was wondering if the experts could give some pointers on how to make the code more efficient while still keeping it simple so others may be able to modify later. I am keeping the ScreenUpdating True as the alternative false will just show Excel as Not Responding to the user until the VB is finished.
Sub deleteReplaceRows()
Application.ScreenUpdating = True
Dim DeletedRows As Integer
Dim lastRow As Long
Dim Arr(7)
Arr(1) = "<PUZZLE>"
Arr(2) = "<%"
Arr(3) = "%>"
Arr(4) = "Response."
Arr(5) = "</PUZZLE>"
Arr(6) = "<HINT>"
Arr(7) = "<MESSAGE>"
For i = 1 To 7
Do
Set rng = Columns(1).Find(Arr(i))
If rng Is Nothing Then Exit Do
rng.EntireRow.Delete
DeletedRows = DeletedRows + 1...................
View 2 Replies
View Related
May 2, 2008
I have a VBA Macro that loops through about 100,000 rows in an Excel file and removes rows that have a duplicate cell value. The macro takes about an hour and a half to run. Are there any ways to make the Macro run faster? Any ways in general to make VBA macros run faster?
View 9 Replies
View Related
Sep 29, 2011
I have a range of cells ( C2, C5, C8:N1007, P8:P1007 ....) on a worksheet that I want to be able to clear the contents of through a macro - worksheet is entitled Database
I have 3 additional worksheets all of which have summary information on and some of the formulas are huge.
I've inserted a module and produced this code;
Sub ClearContents()
Application.Calculation = xlCalculationManual
Range("C2, C5, C8:N1007, P8:P1007, ....").ClearContents
Application.Calculation = xlCalculationAutomatic
End Sub
Because of all the 3 summary page formulas the code is taking a few seconds to run.
I'm new to VBA and just wondered whether I could switch the calculation setting for the entire workbook onto manual at the beginning of the code and then switch it back to automatic at the end of the code? Think this might speed things up.
View 5 Replies
View Related
Mar 11, 2014
I have a Workbook on a shared drive, with a button that runs a macro.
Is there a way to disable this if anyone makes a copy of this workbook, or does Save As? (If it is not the original workbook).
View 6 Replies
View Related
Oct 9, 2009
I have a Listbox that outputs data to Column A. Is there a way to auto populate Column B with 4 items for each Entry in Column A?
The 4 items that will populate in Column B will always be the same.
I have attached an example to better explain what I am trying to describe.
View 6 Replies
View Related
Feb 20, 2009
Let's say I have a dataset with blank or X in column A, and a dollar value in column B. I want to sum the dollar amounts for column B only if there is a corresponding X in that row in column A. The way I currently do that is by creating a new column C, making a if statement to display the value of B only if there is an entry in A, and then doing operations on that new range. I figure there has to be an easier way. I don't know how to use VLOOKUP, despite trying to read the helpme a bunch of times. Is that relevant?
View 8 Replies
View Related
Jun 27, 2009
I have a workbook, 'MyWb.xls' and a sheet with buttons to which I attach some sheet macros. When I assign a macro to a button I pick the appropriate reference, e.g. Sheet2.MyMacro, and this gets recorded by default as MyWb.xls!Sheet2.MyMacro
When I create a new single sheet workbook with the instruction ActiveSheet.Copy, I end up with a new workbook, say 'Book2' along with the buttons as expected. However the macros attached to the buttons still refer to the MyWB.xls file and not the new 'Book2.xls'. As part of the process of creating the new book I've tried redefining the macro with the instruction
View 4 Replies
View Related
Mar 20, 2007
I've created a custom toolbar and populated it with a custom button. I have created a macro and it resides in ThisWorkbook. The macro is assigned to the custom button and the toolbar attached to the workbook. So far, so good. If I copy the workbook and rename it, when I click the button to run the macro it launches the original workbook and runs the macro. The macro effects changes on the renamed workbook copy. The macro name found in the assign macro dialogue starts with the original workbook name. The same effect if the macro resides in a module or sheet. Any known way to have the macro name change along with the renaming of the workbook such taht it refers to the currently opened workbook macros? Regards.
View 5 Replies
View Related
Jan 4, 2012
I have a simple list of data (Name, Room, etc.) in a spreadsheet. Each day I click on the spreadsheet tab and create a "copy" and (move to end). This creates a spreadsheet for the next day's data.
My problem is that the macro I use to sort this data never works in the newly created spreadsheet within the same workbook. As usual, I'm sure it is something relatively simple that I am overlooking.
View 1 Replies
View Related
Dec 27, 2012
What i want here is the total for a particular id.
View 3 Replies
View Related
Feb 27, 2009
I have set up a file with drop down selections from column B to R copied down to row 3000. I am sure this is why the file is slow when you make a selection. How does one speed this up?? Is it possible?? I created it using Excel 2007 saved as a 2003 version.
View 3 Replies
View Related
Apr 30, 2007
I'm currently trying to insert the copy range of one worksheet to another. Let's say I have 3 worksheets, one main and two additional info sheet. I want to search column C in main worksheet for blank cell and use column F in the same row as the worksheet name which I want to copy from, then use column E from main worksheet as the keyword for the range I want to copy(range from column E to column Z). But when inserting, I want the keyword in main worksheet such as 'G' in the same row as blank cell to match the 'G' in copied worksheet. Because the 'G' in main worksheet is not in the same column for each row, I try to search the forum but can't find the inserting part. I also attach the example workbook.
View 3 Replies
View Related
Oct 10, 2013
I have 2 sheets, sheet2 have data consist of 5 columns I want to vlook up by vba code so the data from sheet2 with reference here is (ID) search in sheet1 and update the data from sheet 2 to sheet 1 like below. i have found a similar code but it updates only one column that is B cloumn i want to update C, D, E columns and rows by the below code. see the below code.
Sheet 2
Id, beginning, principal, interest, endbalance
123, 10000, 1000, 50 , 9000
789, 8000 , 400 , 150, 7600
245, 5000 , 1000, 200 , 4000
456, 4000 , 1000, 1000 , 3000
789, 2500 , 500 , 100 , 2000
Sheet1
id beginning principal interest endbalance
123 10000
789 8000
245 5000
456 4000
786 2500
Ifound below code suitable for the above query but this is only execute column b , I want to execute c, d, e columns how can I do this.
Sub ADDCLM()
On Error Resume Next
Dim Dept_Row As Long
[Code]....
View 8 Replies
View Related
May 1, 2014
i need to group the values based on A, B, C columns.
which means all "Chickpea_B-1_11area_3d.csv " will have same timestamp and mean_area_3d values, only D column vlaue changes.....
in the same way, all "Chickpea_B-1_12area_3d.csv" will have same timestamp and mean_area_3d values, only D column vlaue changes.....
now i need all Chickpea_B-1_11area_3d.csv values to be grouped into one. and chickpea_B-1_12area_3d.csv values to be grouped into one..
if you see the output sheet, you will understand clearly, which i did manually for your easy understanding...
i did manually and also attached the sheet for your kind reference and easy understanding... where i grouped all Chickpea_B-1_11_area_3daver_domain_area, fill_factor, leaf_angle_theta, plantHeight, projected_leaf_a into one.... where b and c cols are same for all the D E F G H I Values. in the same way for Chickpea_B-1_12_area_3d and all other below values to be grouped.
like this i have to do for 1000 files. please find the attachment...
View 12 Replies
View Related
Jun 4, 2014
Attached is my sample data Sample Data_Combine Sheets.xlsm.
What the code in the attached sheet does is that it combines the data in Sheets Set1 and Set2 into the worksheet named "Combine".
Both input sheets share same columns. Code I use is as below:
VB:
Sub BuildPivotData()
Dim wrsht As Variant
Dim i As Integer
Sheets("Combine").Range("A2:AI500").Clear
wrsht = [{"Set1", "Set2"}]
For i = 1 To UBound(wrsht)
Sheets(wrsht(i)).[a2].CurrentRegion.Offset(1).Copy Sheets("Combine").Range("A65536").End(xlUp)(2)
Next i
End Sub
I want to amend the output in such a way that when the macro is run , in the "Combine" worksheet an additional column is added at the start(col A) with header as "Category".
The category should be nothing but the input Sheet name i.e Set1 or Set2.
Here is an example ... Output Sheet.xlsx.
View 3 Replies
View Related
Feb 13, 2014
The code I have below is relevant to this spreadsheet Copy of Invoice test.xlsm
In the code below I marked the script that has issues in red. The code takes the last row and the last column and works from there. This works fine with single commands, however, when I take a cell (based off the last row and the last column) and try to give that cell multiple commands excel sends back an error. When I use this multiple command (see part of code that is marked in red) for a range that is not based off the last column it works fine.
[Code] .....
View 9 Replies
View Related
May 30, 2008
Short and simple. What is the quickest, easiest & most efficient way to find the first blank cell within a column using VBA?
View 9 Replies
View Related
May 28, 2014
I'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").
I believe are in coding the destination range since I can get it to work for just one cell in B! Below is my code that is not working...
[Code].....
View 7 Replies
View Related
Jun 24, 2009
I have this macro below and would like the code changed to find the "Customer Number" column by name rather than by column B. Note that the "Customer Number" column will always be somewhere in row 1.
View 12 Replies
View Related