Speeding Up An Auto-delete Macro
Jul 14, 2008
Delete rows where column A and B are duplicated.
However, this runs extremely slow. I had to stop it after about 10 min, as it only got to about row 1000 of 20,000.
Sub Delete_Dupes()
Dim rw1 As Long: rw1 = 1
Dim rwx As Long: rwx = rw1
Dim stepx As Integer
Dim co1 As Integer: co1 = 1
Dim co2 As Integer: co2 = 2
Dim bool1 As Boolean
Dim bool2 As Boolean
Dim bool As Boolean
Dim count As Integer
count = 0
Do Until Cells(rwx, co1) = ""
stepx = 1
If rwx > rw1 Then
On Error GoTo NewCrit
bool1 = IsError(Application.WorksheetFunction.Match(Cells(rwx, co1), Range(Cells(rw1, co1), Cells(rwx - 1, co1)), 0))
bool2 = IsError(Application.WorksheetFunction.Match(Cells(rwx, co2), Range(Cells(rw1, co2), Cells(rwx - 1, co2)), 0)).................
View 9 Replies
ADVERTISEMENT
May 22, 2014
I want to filter 4 worksheets, but it takes a lot of time, something like 5 seconds, I need it to be at least twice faster.
I'm using this formula: [Code]......
View 2 Replies
View Related
Mar 13, 2008
This may be a long shot, but I've been working on a macro today which runs a few Vlookups, does some copying and pasting and some re-aligning....basically nothing too complicated. The Vlookup is looking through approx 45,000 records and returning about 200 - 300 on average, this info returned is company name, address, contact info etc etc.
This macro is taking a ludicrously long time to run, I've checked through and removed any coding that I think is unnecessary without altering the final outcome of the macro.
It still takes ages. Has anyone got any ideas why this may be, or any tips of speeding it up?
I have a brand new computer, dual core processor, 2 gigs of ram etc so it shouldn't be performance of the machine slowing it down, and I ran it with the least applications open that I can (I'm at work so can't close everything, but none of the other applications are memory hogs or anything). I'm running on Office 2003 on Windows XP.
View 9 Replies
View Related
May 13, 2008
I am running a rather large simmulation where I have 12 for loops with each running between 0 and 100% with variable steps and the where the sum of the 12 variables has to sum to 100% and sometimes combinations of the sums of a few of the 12 have to sum up to specific values. That part works, now I am trying to get this to run faster.
Question:
Is there a different design that might be able to run this process more efficiently?
If not, is there a way to speed up the process by letting the computer know that all these variables are between 0 and 1 (or 100%) and that I only need 3 digits after the decimal if the value is in % form?
(the only problem with the rounding or cutting off the decimals at the end that I can think of is that it will only speed up the process a bit but will also make the summing upto 100% or any other value difficult)
View 9 Replies
View Related
Jun 28, 2007
I need help with speeding up my macro. I have been building this using alot of the recorder and the maco is just horribly sluggish. I don't know if it is possible, but it seems like it is. I need the macro to obviously work the same way. The way it has been recorded, the order of operation is important. But if any one knows how to do this better it would be a huge help. The slowest part is during the insertion of the formulas into col. J,K,&L takes like over 7min -- Macro does work perfect though.
View 9 Replies
View Related
Aug 23, 2007
This code is pretty slow if most rows have to be hidden. I'm certain there is a faster way to do this but I'm not seeing it.
What am I doing with
For each row from 12 to 236 I'm looking to see if there is data in any cell in columns A:F, H, & J. If not, I hide the row. I'm starting at row 236 and working up to 12 continuing to hide rows until I find data or I reach row 11. As soon as any row has data or row 11 is reached the the procedure ends leaving all remaining rows visible ...
View 9 Replies
View Related
Aug 27, 2009
Ive found a few ways to speed up the macro in my workbook thats taking about 40 minutes to process usually.
One way - Press Escape and it goes about twice as fast! - Wy does it do this?
Another way - Inserting these lines into the code -
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
View 9 Replies
View Related
Mar 14, 2013
1. I need to paste data that is copied from an internet window into sheet1 without selecting the cell so that I can continue working on a data in sheet5. The macro prevents me from editing sheet5 in an orderly fashion because when it goes to paste the data into sheet1 it selects the cell in sheet1 interupting what I am trying to manually do with the data in sheet5. How to paste data into a cell withou selecting the cell so that my program stops interupting what I am doing in a different sheet?
2. My macro is meant for real-time defect monitoring in a production facility. The macro opens an IE window that contains the defect occurences, copies the data, pastes it in sheet 1 then sheets2 and 3 analyze the data, closes the IE window, and then waits for a time period before repeating the process. This macro is so slow though that it is hard to work on this file while the macro is running. How to run the macro in the background and speed it up so that I can continue working on Sheet5 of the file while the macro repeats itself over and over again? Here is my code.
The variable website is determined by earlier parts of the code that direct the macro to a specific network website.
Sub Half_Hour_Data
1
'Open Internet Window and Navigate to Website
Set myIE = CreateObject("InternetExplorer.Application")
myIE.Navigate Website
myIE.Visible = True
Application.Wait Now + TimeSerial(0, 0, 10) needed to wait until the page loads
[code]....
Is there a way to speed up the macro by removing the timeserials and just telling it to continue when the task is complete. Then run the whole thing in the background so I can work on another sheet while this program runs?
View 1 Replies
View Related
Jul 7, 2009
i'm working on floyd algorithm macro for a project,
problem is, the spreadsheet matrix is huge, it's a 1043 x 1043 spreadsheet
And this is taking wayyy too long, i've been waiting for almost 2 days already..
here's the file :
http://ifile.it/6v2j39f
(You start the macro using "run" command in "input" sheet)
So is there any way to speed it up?
Or if anyone got a supercomputer, could you help me process this? :p
I'm thinking about changing the 10^5 value into infinite value, but i can't find a way to display infinite value..
And here's some reference for Floyd's algorithm,
http://ifile.it/12p5zle
I have tried to split the algorithm to make it process one iteration at a time, but i've been waiting like 2 hours with no result,
and that's for 1 iteration, can't imagine how long it takes for 1043 iterations
You can do this by deleting the.
View 14 Replies
View Related
May 28, 2008
I have a excel file in which , there is probably a macro which runs when i open the file. The macro checks for a particular file on the D: drive.
I want to delete this macro. The problem is i cannot delete this macro, as it is not appearing in the Macros List.
Also i know i can disable this macro, by pressing the shift key whenever the file is opened.
But i want to delete this macro permanently from this file. I have tried various options like pression Alt-F11 & to locate the macro in module etc & also tried to locate it in the Workbook open event handler. But I cant see the macro.
How do i locate this macro & delete it.
View 9 Replies
View Related
Jul 10, 2008
I browsed the first few pages of searching for "row delete macro" and couldn't find anything that suited my needs very much, so here goes:
What I need to do is automatically delete rows where the entries in column B (date) and column E (string) are the same. It is a very large group of data, so filtering is not exacly an option here.
Here is the twist on it...
I have a column F that contains either "Accepted-Active" or "Accepted-Closed". If I delete a bunch of rows that contain the same column B and E entries, I want to save the row that contains "Accepted-Closed", otherwise I don't particularly care which of the rows is saved.
View 9 Replies
View Related
May 28, 2009
I'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.
Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"
UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................
View 2 Replies
View Related
Jul 29, 2014
I need the tabs of a project action log to auto-populate and auto-delete in a master log. (So when something is added or deleted in a tab it is added or deleted on the master) I use excel a little bit for work and personal finance purposes but I have zero experience with macros or VBA.
View 1 Replies
View Related
Jan 7, 2008
Within a data validation selection, are you able to do both Auto Complete and Auto Delete? I have this posted at another forum [url]but have not been able to find a solution (a copy of the file, test. zip is there as well). I am not sure it is possible, or, at least I have been unable to get it working. I can do each, but not both.
I have also been utilizing:
[url]
[url]
View 5 Replies
View Related
Jul 16, 2007
I've been working on the following code and need to find a way to speed it up.
The premise is that I have a list of columns (From C2 to potentially EH and beyond) and these columns will contain the values Y, N, end or nothing. I'm looking for the quickest way to zap along the columns and delete all that have an N and to stop when end or "" is reached.
There are 2 versions as I'm not sure which one is best ...
Range("B2").Select
LastColumn = Range("A1").End(xlToRight).Offset(, 1).Column
For k = 2 To LastColumn
ActiveCell.Offset(0, 1).Select ' right 1
If ActiveCell.Value = "Y" Then Next k
End If
If ActiveCell.Value = "N" Then
ActiveCell.EntireColumn.Delete 'If employee has no sickness then delete column
ActiveCell.Offset(0, -1).Select
End If
If ActiveCell.Value = "end" Then Exit For
If ActiveCell.Value = "" Then Exit For
Next k
or a longer version
StripSickness:
Range("B2").Select
ActiveCell.Offset(0, 1).Select ' right 1
PrintView = ActiveCell.Value 'Y/N whether employee has data
If PrintView = "Y" Then GoTo StripSickness
If PrintView = "N" Then GoTo NotNeeded
If PrintView = "End" Then GoTo Complete 'last column shows "end"
If PrintView = "" Then GoTo Complete
NotNeeded:
ActiveCell.EntireColumn.Delete
ActiveCell.Offset(0, -1).Select
GoTo StripSickness 'once employee is deleted, go back to stripsickness process
Complete:
Range("a1").Select
View 9 Replies
View Related
Jul 17, 2007
I've been working on cleaning up some code that copies particular information from one sheet (as the original must remain unedited) and then edits out irrelivant information.
However I've noticed that my "before" code moves far faster than my "after" code, which is odd considering that I've cleaned out a lot of excess code and the end results are the same.
I attach an example of my old fast code and my new slow code - can anyone advise where I have gone wrong ? Run the fast code first and then the slow one - you'll see the difference!
View 9 Replies
View Related
Aug 20, 2007
first, id like to thank the board for the help on setting up an array or ranges.
ive been able to get my code to work but its painfully slow...if anyone had any recommendations on speeding it up would appreciate it...i was playing around with using formulaarray instead of writing and reading the data on each loop but cant get it to work...and i'm not really even sure thats the right way to go.
i copied the entire code below...what i was doing was taking 8 time series and looping thru each one to calculate some basic statistics...i ran the function EDFlyRateFromPrice to calculate another series of data which i then run the standardize, max and min functions on.
View 9 Replies
View Related
Jun 23, 2008
This part of my macro can be improved as it runs through over 100 sheets and takes a while?
Sheets.Select
For Each sh In Worksheets
With sh.PageSetup
.Zoom = False
.LeftFooter = "&F" & Chr(10) & "&A"
.CenterFooter = "&P of &N"
.RightFooter = "&D"
.FitToPagesWide = 1
.FitToPagesTall = 1
.LeftMargin = Application.InchesToPoints(0.15748031496063)
.RightMargin = Application.InchesToPoints(0.15748031496063)
.TopMargin = Application.InchesToPoints(0.393700787401575)
.BottomMargin = Application.InchesToPoints(0.354330708661417)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = 1
.BottomMargin = 1
.Orientation = xlPortrait
End With
Next sh
View 9 Replies
View Related
Mar 4, 2014
I am putting together a workbook which calculates a rota for 5 days that shows overall headcounts every 15mins.
It currently has 12672 cells containing SUMIFS.
But this takes a very long time to calculate.
Is there a faster / more efficient way for me to approach this task?
Can I attach the workbook so you can see what I mean?
View 1 Replies
View Related
Jan 15, 2007
Did not want to hijack Jonny's thread about ways in which to update and streamline code to make it run faster. The update speed of my first attempt at a macro is woefully slow - 5 minutes for 2.5K records. I was wondering if some of you more knowledgeable folk could look at my code and make suggestions as to how it could be modified to run faster.
When I ctrl-break out of it I usually end up in a private sub that concatenates columns A & B in Column C (see below). When it resorts the database it must keep triggering this concatenate sub which (I think) is slowing things down.
Concatenate Private Sub:
Option Explicit
Private Sub CommandButton1_Click()
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub ......................
View 9 Replies
View Related
Jul 13, 2009
I want create an conditional delete macro i.e.: if in column a - b - c the cell is not coloured = delete.
View 3 Replies
View Related
Jun 12, 2008
How would you make a macro that would delete the line if a field in that line say for example K1:K100 had "XX" or "XXX" in it?
View 10 Replies
View Related
Mar 23, 2012
how to automatically complete with Excel the following task:
1. I have two email databases in Excel: a master database and opt-out database.
2. I need to remove from the Master database emails of opted-out people.
3. Since the databases are large, I'd like to use a relevant Excel function to do that automatically.:
In the master list (column B) I have all the emails from the Master list. I have copied in the column C of the Master list the emails of all who opted-out. I need to remove opted out emails (listed in column C) from the master email list (column B).
View 1 Replies
View Related
Jun 19, 2009
I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.
Sheets("Tel").Select
ActiveWindow.SelectedSheets.Delete
View 4 Replies
View Related
Apr 24, 2009
i'm looking for is that I have two lists via Data Validation, and when someone changes their selection in list A, i'd like for list B to auto-delete whatever value it had in it.
Also, on a different topic, i'd like to know how to hide certain sheets in a workbook from public view.
View 3 Replies
View Related
Sep 12, 2008
1) Highlight column A, and do a search on the word "Item"
2) Once it finds the word item, move 2 rows above it, and delete all rows above the word "Item"
The word Item at times can be on any row, and its very random, but its always in column, A. I tried doing it with a regular macro, but it seems to remember the row that I used, rather than two rows above the word "Item".
View 9 Replies
View Related
Oct 12, 2008
vba to auto delete cells which is filled with color. e.g if from c39 to d39 is filled with lavender i want a vba to delete cell c39 to h39 until all cells with lavender is deleted. If there are other threads like this please direct me.
0.00 00 0.00 00 0.00 00 0.00 00 0.00 00 0.00 00 0.00 00
View 9 Replies
View Related
May 1, 2008
I'm working on a time sheet, and to make it easier to enter time i created a macro that sets the auto correct to correct a "." to a ":", but when the workbook closes i want to run a macro that deletes the auto correct rule.
View 13 Replies
View Related
May 20, 2009
it's possible to select a group of cells, or a column, and then have specific words (and the colon), like Email: Tel: Fax: and URL: found and removed?
I've got a list of contacts one after the other in a column like this
John
Accountant
456, Hillside Drive, Hamley
Tel: 09189374884
Fax: 0284764654
Email: john@email.om
URL: ww.johntheaccountant.com
The number of lines per contact isn't always the same.
Can it be done without Macros?
View 9 Replies
View Related
May 25, 2013
I have a spreadsheet that I use throughout the day and have to send an email to another person when a customer makes a payment. The number of products they pay for will be different each time and I have the code to copy the right cells into an email and the code to insert the right number of rows for how many things the customer is paying for however as I use it multiple times through the day, I need the code to insert the right number of rows, copy it over to the email then automatically delete the rows it has just inserted so it is back to basics so I can use it again.
The code I have for the inserting the rows (from searching through forums) is:
Sub PRows()
Dim NextRow As Long
Dim NrOfCopies As Long
Dim i As Long
Const NrOfCopiesDefault = 1
Const NrOfCopiesMaximum = 9
[code]....
View 1 Replies
View Related