Speed Up Macro Deleting Every Nth Line
Jul 4, 2012
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
View 9 Replies
ADVERTISEMENT
Oct 24, 2012
I use
Sheets("DonorContacts").Select
ActiveWindow.SelectedSheets.Delete
to delete a tab in a file. The macro then prompts me with something like "Are you sure? There may be data in there?" How can I get the macro to answer that question?
View 1 Replies
View Related
Apr 20, 2006
I have been playing with some code and was trying an alternate way. My first approach was matching numbers, inserting rows when needed and then deleting rows when needed. I wasn't satisified with this approach because...Well to be honest, I figured it out on my own and I don't think I'm confident enough in my coding skills yet.
Someone suggested that instead of inserting rows, copy the data below the existing data, sort and then do the rest. I did this, but by doing this it gives me over 1, 000 rows of data to loop through and delete duplicates. Yes, my code was shortened and seemed less complex, but it actually took longer with the new approach.
Inserting Rows = 4.6 seconds
Sorting then deleting dupes = 7 seconds
Does deleting multiple rows of data take more time then inserting rows?
View 4 Replies
View Related
Jan 28, 2009
A have a worksheet in excel which was copied from pdf.
in each cell number of different lines, the data appears on a new lines. the same as using the new line character. (ALT + ENTER)
The document is huge. well over 3000 individual cells, and generally 4 or 5 lines in each one. Is there anyway i can globally change the whole documents. Just deleteling all the new lines??
Using excel 2007 on windows xp..
View 14 Replies
View Related
Apr 30, 2013
I have a worksheet that I need to be able to easily copy and paste the formula/formatting from the row above, but not the Values entered, (if any). I am using columns A to DG.
I just really need to be able to copy all the formula in these cells down - nothing exciting or fancy.
I've tried formatting as a table as per some searches and that was difficult! My column headings changed, and when I inserted a row, two of the formula's wouldn't copy down - plus the shading and borders went wonky in places.
View 2 Replies
View Related
Dec 8, 2007
i hv following code
(i use generate macro)
my question is how to arrange the code from one line to multiple like :-
following code show in excel macro environment is one striaght line.
' Create new var on yr , and replace 2006 to CY06.
ActiveCell.FormulaR1C1 = _
View 9 Replies
View Related
Nov 4, 2008
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.
View 5 Replies
View Related
Sep 24, 2008
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.
View 9 Replies
View Related
Sep 4, 2009
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.
View 9 Replies
View Related
Mar 2, 2010
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 ....
View 9 Replies
View Related
Dec 14, 2006
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.
View 5 Replies
View Related
Sep 12, 2009
Does anyone know a more efficient way to run this code?
View 7 Replies
View Related
Jan 11, 2010
I've got a rather involved macro that's running kind of slowly, and I would appreciate any help I can get speeding it up. It's in two parts; the first is to create and email a report, the second is to format so it's pretty for printing. The full codes for both routines is pasted below.
The email part I developed first and it runs pretty quickly. Afterwards, I added the second macro, which is called halfway through the first.
Stepping through the code in the second macro, the problem I see is in this section, the setup for setting the heighth of merged cells in the report:
View 14 Replies
View Related
Jun 4, 2008
I run a simple macro loop to clean some data across nine columns. The purpose is to collapse the data in the columns so that column 1 has the first value found in that row, for the set of columns. For instance, if columns 1-4 are empty, it deletes / shifts everything left until the first column is not empty. Then it goes to the next row and repeats. Data can range from a few rows up to 6000.
[I have a period in the data as the cell content to evaluate]
Sub A_Rollup_collapse()
StartT = Now
Dim Col As Integer
Col = Range("IV1").End(xlToLeft).Column - 9
LastR = Range("A60000").End(xlUp).Row
Application.ScreenUpdating = False
For R = 2 To LastR
Do While Cells(R, Col) = "."
Cells(R, Col).Delete Shift:=xlShiftToLeft
Loop
Next R
EndT = Now
Application.ScreenUpdating = True
msg = "Done. Duration: " & Format(EndT - StartT, "h:mm:ss")
MsgBox (msg)
this macro (B) runs after another macro (A) that populates the nine columns with data using vlookups. Macro (A) It builds out a chain of information from col 2 to col 9, converts to values etc. Nothing odd.
When macro (B) is called right after running macro (A), it can take about one minute for 500 rows of data.
When I save and close the workbook, reopen it and run macro (B), it only takes one second.
When I insert a ThisWorkbook.Save between the two call statments, macro (B) still takes over a minute.
A minute is not too bad but when I'm dealing with thousands of rows, the difference is more like 30 seconds vs. 9 minutes which is a problem.
View 9 Replies
View Related
May 26, 2007
ran the following macro on the new computer and on two other computers...
View 9 Replies
View Related
Jun 6, 2007
It takes all numbers in column 4 starting with Row 15 and deletes all duplicates. It then shows the number of times the number was duplicated and puts this number in column 3. MY PROBLEM: The macro searches each line and takes FOREVER! I have data with thousands of lines. I already tried the screenupdating method which really doesn't help that much. Is there possibly a better code for doing this?
Sub Factor()
Dim sID As String
Dim sOldID As String
Dim lLastRow As Long
Dim lrow As Long
Dim lcount As Long
Dim lLoop As Long
lLastRow = ActiveSheet. Cells(Rows.Count, 1).End(xlUp).Row
lrow = 15
sID = ActiveSheet.Cells(lrow, 4).Value
sOldID = "ActiveSheet.Cells(4, 15).Value"
lcount = 1
lLoop = 1
Do While Len(sID) <> 0
If sID <> sOldID Then
If lLoop = 1 Then.................................
View 5 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
Jan 8, 2013
I have recorded 7 different macros and then combined them all into one macro to achieve one end result. I am not sure if you can just look at the codes to determine different ways to improve them or if you need the excel spreadsheet as well.
View 2 Replies
View Related
Jan 13, 2010
Attached is a workbook that takes an imported text file, inserts rows with text.
It is SLOW, you can watch each line being inserted with the text, I would think with such a small sample dataset it would be much much quicker.
Information: See attached xls file.
On sheet1 is the imported data, sheet2 is a copy of the imported data so one can copy and paste to sheet1 as needed with out re-importing for test purposes and only for this query. (Sheets2 thru 3 are not used otherwise).
There is a command button on sheet one which will run macro "aaa". If you run this you will see how slow it is and exactly what it is doing.
I am simply looking for a way to speed this up, I have some files that are 10 times the size of the sample data and they take 10 or more minutes to run.
View 10 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
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
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
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
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
Jul 8, 2009
I have a macro that needs a major improvement in performance. I have a Quad Core 2.67GHz single processor computer and with the current logic it calculates 20 values per minute. I have data sets that can be up to 400,000 data points, which means it will take 333 hours. The attached Sample file has over 9000 point. For this data set it takes 7.5 hours.
The core logic of the macro is to extract what I call “Break Point” into column M and “Time Difference” between the Break Points to column O.
I got the code from this forum (thank you DonkeyOte) and made some modifications. The modified logic does the following:
1) The user inputs the starting cell. In the Sample I use G200.
2) From cell defined in button, It moves down one cell in that column and compares that value to the original cell.
If the value is greater than original cell then there are 2 possible outcomes:
a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value to the column M (Break Point value) and copy the Time Difference Value to column O. “Time Difference” is calculated as the difference between the 2 point breaks in Column A. In this case I’ve hardcoded 0.003472222 to get 5 minutes which matches up with each incremental time in column A, but I would like the Macro to automatically calculate that by the difference between Column A values.
b) move down 1 cell in column and if that value is greater than original cell, then move down again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value to column M and copy the Time Difference Value to column O.
Once lesser value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
At any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column G.
OR
Here's the logic now in reverse I need:
If after moving down 1 cell of original cell, the value is less than original cell then logic has the following 2 outcomes:
a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O..
b) move down 1 cell in column and if that value less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
Once greater value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value lesser than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
Again, at any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column.
Once all the Break Points and Time Difference between Break Points are extracted for each value in column G, the AVERAGE, STDEV and MAX values of column O are calculated in column Q, R and S.
There is a loop that controls the execution of the core logic until the last value in column G. In the macro I’ve hardcoded last row being 9171, but I’d like the Macro to figure out what the last row is automatically.
I know this description is a bit long but I’ve worked for many hours on getting it work properly. I just need some help to make it run much faster. I hope someone can help me out on this. I have the right logic, just need the speed now and I really can’t figure it out.
Attached is a sample files demonstrating showing how the logic to flips back and forth as moving down the column starting in cell G200.
The sample shows the results first 4 loops (Rows 200 to 203). The values in M and O are the results of the fourth loop.
Again, assistance is very much appreciated because I've taken this as far as I can with my limited experience.
EDIT - I can embed the Macro, but it'll probably much easier to actually see it in the sample file.
View 14 Replies
View Related
Dec 12, 2006
My macro works fine but I'm interested in seeing if I can speed it up. What I'm doing is starting at the bottom and comparing it with the row above and if they match in font color it will fill the top with orange and delete the bottom. This works but usually takes several minutes.
Public Sub ADMINCompareList()
Dim varTest1, varTest2
Dim lng As Long, i As Integer, iTest As Integer
Application. ScreenUpdating = False
Worksheets("ADMIN").Activate
For lng = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If Not Range("M" & lng).Font.Color <> Range("M" & lng - 1).Font.Color Then
Goto newrow
End If
varTest1 = Intersect(Range("J:W"), Rows(lng))
varTest2 = Intersect(Range("J:W"), Rows(lng - 1))
For i = 1 To 14....................................
View 3 Replies
View Related
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
View Related
Oct 3, 2012
I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.
However, if the user is not online, the query is unable to refresh and the macro just hangs.
Is there a code which will enable me to say " if unable to refresh then move on to the next line"?
here's the code below.
Private Sub Workbook_Open()
Sheets("Houselist").Activate
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Front").Select
Range("A1").Select
End Sub
View 2 Replies
View Related
May 2, 2014
I am trying to write a macro that takes the value in cell B2 from a workbook called ""numbers" in a worksheet called "summary," and creates a new worksheet in a workbook called "filestransfer" that is named the same thing as the value in cell B2 from "numbers." That part is done.
View 2 Replies
View Related
