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
I currently have a macro set up to delete rows if a certain user selected value is not found in a certain column. It works fine if the sheet isn't overly large but the problem is we have some spreadsheets with 25,000+ rows and it takes time to loop through - I'm not sure if there is even a way to make it faster.
Currently it works by looping backwards on the sheet checking each value in the cell versus an array containing the user selected values. If a match isnt found it deletes this row.
'y = long value representing row 'x = counter for each item in user selected array 'wsSheet = worksheet we are using 'rowLast = last row on spreadsheet 'arrSearch() = string containing user selected items
i have screenupdating/calculations/events turned off - i just didnt know if there was a faster way of looping through
i just thought about instead of doing an array maybe joining the array into 1 string and using a "like" comparison check to see if a match is found - would save me from having to constantly loop x * y amount of times - not sure if this would work or not. ill post back if it doesn't
modify the delete row macro below. Unfortantely this is where my brain cell sometimes dies and the easy answers are as apparent as they should be...
Ideally what I am looking to do is have a growing list of "IDS" in one worksheet (call it delete IDS) that will be used to loop thru the sheet named "NRs" and delete the the IDS from that worksheet.
Sub DeleteNames() 'using set column Sheets("NRs").Activate Dim RngCol As Range Dim i As Range Set RngCol = Range("A:A") For Each i In RngCol If i.Value = 156916233 Or i.Value = 600093 _ Or i.Value = 156970571 _ Or i.Value = 156948409 Or i.Value = 30560 _ Or i.Value = 163841503 Then _ i.EntireRow.Delete Next i End Sub
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;
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...................
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?
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.
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.
I have a spreadsheet that has two different data sources (i.e., A and B). The amounts of these data sources (i.e., the number of columns) varies from sheet to sheet. I have to calculate the averages of these data sources independently and together. Because this data is spread across twenty or more spreadsheets, these calculations can be time-consuming. I want to do the following: 1. Locate the last occurrence of the first data source "A" in row and then insert a column after that cell. 2. In that cell, I want to get the average of each data source for each row of data (i.e., there are always 19 rows of data). I want to do the same two steps for the second data source "B". Then, I want to insert a column after the "B" average and this column will be used to get an average of each row of data from A and B together. Please keep in mind that the number of data sources for A and B varies from sheet to sheet. So far, I am working on code to try to " find" the text in a range (i.e., find the last instance of "A"), but I cannot figure out how to get it to get it to stop at the last occurrence and then insert a column. I have some ideas about how to calculate the average, but any of yours are much appreciated. Also, the row with the type of data (i.e., A or B) is named because this function is part of a larger macro. Therefore, it is relatively easy to get to it. I have attached a version of the file that displays how I want it to look. The code I have thus far is:
which then compares the three-letter code with a list in the following format to create a new text file where data is only included if the code is on the list. Since I added the range search to the code it runs VERY slow
This time ive created a visual basic form that asks for a row number and when you put a row number in this form it deletes what is in column A, B and C of that row and shift the rows up so that there are no gaps between the data in the rows.
I need to know what code i would have on the 'delete' button of the form.
If you want me to describe it any further i would be happy to, just ask.
I have developed (plagiarized) the following code but it is very slow. how I could speed this up? Eventually, the range may encompass more rows, and there are three separate ranges this code will act upon, so how to speed things up.
Sub DeleteBlanks1() Dim i, Endrow As Long Endrow = Range("K2000").End(xlUp).Row Application. ScreenUpdating = False For i = Endrow To 2 Step -1 If Cells(i, 11).Value = "" Then Range(Cells(i, 8), Cells(i, 13)).Delete End If Next i Application.ScreenUpdating = True End Sub
My question is can I use cpu clock or some other means temporarily as an indicator before and after sections of code to display run speed until I find the best optimised code solution.
I have a very complex report with a variable numbers of rows in multiple sections. I am evaluating whether to display each row with a formula in the first column (those that I don't want to display have a "H" in the first column).
The following code works fine, but it takes forever. Ideally I wanted to trigger the code on the Worksheet_Activate() event but its so inefficient that I can't do that.
I have a triple loop I would like to create a triple loop code at the moment
m = Worksheets("txt"). Range("AM5").Value
n = Worksheets("txt").Range("AM1").Value o = Worksheets("txt").Range("AM2").Value p = Worksheets("txt").Range("AM3").Value q = Worksheets("txt").Range("AN1").Value r = Worksheets("txt").Range("AN2").Value s = Worksheets("txt").Range("AN3").Value t = Worksheets("txt").Range("AO1").Value u = Worksheets("txt").Range("AO2").Value v = Worksheets("txt").Range("AO3").Value
Worksheets("txt").Select For myRow = 1 To m myCol = 5 Worksheets("txt").Cells(myRow, myCol).Select With Selection If .Value = "" Then .Value = n Else: .Value = .Value End If End With Next myRow
In my attachment, I had try to create a PO system that will show a dialouge box and capture input data of the dialogue box into the worksheet.
However, I am not able to make the macro recognise any manual deletion from the worksheet (e.g. delete any row in between the full data). Hence, when I input new data, the row will start from the last register counting of rows (i.e. Range("g1") in my attachment.) and it will leave a blank row after the last row of data and so on...
I will also like to know if I need to fix the "date" input to select from a "calendar" pop-up and input the date format into (e.g. year into column a, month into column b and day into column c) 3 separate columns. Is it possible?
if column E is "Annuity", you can type a value in column J and it runs a formula to calculate a value for column K. If E is not "Annuity" it clears the contents of J (which should clear K). What's not working is when column J gets cleared or manually deleted, column K must be blank. The worksheet_change event is not working for deletion of column J.
Im looking for code for a button that asks for a Record number, and it will delete the record. But the record number is written in cells as 'Record No.1' 'Record No.2' etc. But i want the messagebox to ask for a single number and it would delete that record. Now the position of the records is what will be the difficult bit about this code, but it has a pattern.
For Record No.1 i need deleting cells C2:J100 For Record No.2 i need deleting cells L2:S100 For Record No.3 i need deleting cells U2:AB100
If anyone could give me a bit of code that works for these 3 then i could waste a load of time filling the rest of them out.
The macro clears specific columns in a row when you click anywhere on the row and then hit the command button. It clears the first range and 2nd range in 2 distinct steps, and takes up to 3 seconds.
I am having a big problem with recalculating cells after running a macro. The macro runs at a great speed, but once the calculation is switched back to automatic, the cells are not calculating fast enough. I waited for 4 minutes and it still had not moved past 0%. The worksheet has approximately 9000 rows out to IG columns. 85% of the cells contain an if-then formula.
I have this analyze that is runned by a macro in one workbook, and it starts a analyzing-process in another workbook. The data is picked up in the no2 book, and returned to the first book. It is analyzing lots of workbooks, sometimes up to 1000 workbooks, it means that no2 workbook gets a new name and then saved.
I have once heard that the process could be way faster if the workbooks where the analyzes is processed through not were saved, and I actually dont need the books as long as I got the data into my first workbook.
But Im not sure what in the macro that makes it save the no2 workbook, but I would really like to speed up this process. As it is now I have to start the analyze before I go to bed, and the hopefully it's done when I wake up next morning.
I wrote a macro that works fine, although it runs extremely slowly as if plodding along through all of the cells one at a time. I'm sure the computer is faster than that, so I would like it to chug through more quickly.
I am using some syntax that could be optimized ....
I'm trying to determine the speed of a macro. I searched and have had no luck. recently with some help I reduced my macro speed from minutes to seconds and I was wondering is there code out there that I can record the speed of an existing macro.
So essentially I need a VBA Macro code for Excel 2003 that can take raw data that is input every month via a text document and move it to the next sheet in the workbook. This data is essentially broken down by a "platform" in the raw data section ("P") with monthly totals for each month but it is broken down by platform in total which goes in descending monthly order on the "dat" tabs (which is the final place for the data).
So what needs to happen is the data for the last two years must go from the "P" tab to the "PDat" tab under the correct platform and the correct spot for the newest month. The data that is input each month is in the same format month to month, just starting two rows down from the last month.
At the end of two years, the oldest year of data must be deleted and the other data moved up to the vacant area so that the sheet never grows too large (i.e., at the end of this year 2012 will be deleted and all the data will need to get moved up).
I am dealing with data sets from various instruments that have different sample rates. I am deleting data points I don't need from some of the sets with higher sample rates so that all the data is on the same time scale.
The macro I have is super simple, but incredibly slow. I'm simply deleting every other cell down a column.
VB:
Sub OATcondense() Application.ScreenUpdating = False Do While ActiveCell <> "" ActiveCell.Offset(1, 0).Delete Shift:=xlUp ActiveCell.Offset(1, 0).Select Loop Application.ScreenUpdating = True End Sub
