Need some alternative code that would speed the execution of this macro. My weak attempt runs noticeably SLOW.
Dim cell As Range
For Each cell In Range("TPD")
If IsNumeric(cell) And Not IsEmpty(cell) Then
If cell.Value > 0 Then
cell.EntireColumn.Hidden = False
Else
cell.EntireColumn.Hidden = True
End If
End If
On Error Goto 0
Next
End Sub
why the code below is very slow with the extra if statment in it (marked with **) i have googled around and found that my for staments are correct i have tried screen updating etc. i have added a timer to see what diffrence it makes, what i have is 3 sheets 1st has about 100 rows with a number and time same as second but that only has 2 rows of the same the 3rd sheet has about 100 rows of just a number what the code does is loop through the 3rd sheet (build list) when it finds a number it looks on the first sheet to find the same number then gets the data in the second coloum and so on until it reaches a cell that says stop.
i have added a button to start this process, with the ** line in it takes 311 seconds with out it it takes 0.17 seconds
Going through a loop, I am trying to load pictures into an image box (or alternatively into a label) one by one i.e. going through the loop the first time, I want to load picture 1, then on the second loop, picture 2 and so on. A bit like an automated slide show.
I have written a simple loop and have used the loadpicture function to load the picture into the image box. When the code runs, the image box only gets populated after the last run through the loop. I have tried using application.screen updating function and the image.activate function without success. It is a simple bit of code and I expect an easy problem to solve if you know excel vba well.
Below is the entire code that I am using. It is a simple routine which checks whether a part has started its release process or not, based upon dates. The code works and does what I want.
The problem that I have is that it is very slow, for example it takes 35 seconds to go through 530 lines items. In my (limeted) experiance, based on other VBA doodlings this slow.
I have got the macro below, my problem is that when you run it, it takes 20 seconds or so. In my other larger spreadsheet it was almost instant.
View 4 Replies View RelatedI have the following macro in a worksheet...and it is running very slow. There are other macros in the worksheet and they all run very well. Any ideas by looking at this code why it would be so slow in running?
View 7 Replies View RelatedThe code below runs on a spreadsheet that has approx 600 rows which INDEX and MATCH another spreadsheet which has approx 600 rows. I takes about 4 mins to run.
Sub QC()
Range("T1").Select
ActiveCell.FormulaR1C1 = "Compared CCCD"
Range("V4").Select
Columns("T:T").ColumnWidth = 15.29
Range("S1").Select
Selection.Copy
Range("T1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("T:T").Select...................
I have a macro that isn't very complex but it is running very slow, does anyone know any tricks on how to speed it up?
I programmed a simple macro (attached to a button) that scan the lines in the sheet and uses the function: Cells(y,2).EntireRow.Hide=TRUE (in order to hide certain lines). Usually, It runs fast without problem. But when I print out an area of the sheet, it cause the macro to slow down substantially. (when it runs again). In order to fix that , I had to close the file and reopen it again ( and not print out)
View 7 Replies View Relatedi 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
I'm running some macros that take a bit of time (1000-2000ms), and would like to give the user some feedback as to what's happening.... Ideally, I'd like to show a msgbox-like pop-up window saying something "processing..." while the macro executes.
I have tried using userforms (based on this post Excel VBA Macro- How to Create a Splash Screen for your Excel Workbook); however they halt background execution...
Is there any built-in functionality similar to .statusbar, but more obvious/visual that would allow this? I'm about to experiment with showing/hiding a textbox shape, but this seems a bit sloppy...
This is designed to be run on bid proposals, to calculate special discounts. My idea was that whoever was designing the bid would open this file, which has the discount information and the macro, and then run the macro on the file containing the bid.
This macro works. It worked really fast when it was in my personal.xls and referenced an .xlsx file for the vlookup. It was slower, but still tolerable, when I moved it to the .xlsm file and it still refenced the .xlsx file (an oversight on my part). Now that I fixed the vlookup reference to the .xlsm file, the same one that the macro itself is in, it has slowed to a crawl. What is going on?
I created this macro (below) using the record function but when I kick it off it takes 40 seconds or more to run.
View 4 Replies View RelatedI have written some very basic code to format a report in excel. When I run the code it take a very long time to execute and I receive the following error message at the foot of the page:
Cell (press esc to cancel)
Annoyingly I have had this error before and found the solution on the web but can't remember where. If memory serves my right I deleted some temp files from a specific location on my hard drive?
I'm working on a macro which compares values in particular cells across different versions of a sheet. Unfortunately there are quite a lot of values, and my code is running very slowly.
I'm trying to get the data in a grid, with the file version across the top, and down the left the project name. For each file version I need to look up a specific value for every project listed.
At the moment, my macro is creating a seperate formula for each cell which does a vlookup on the project name to the specific external file.
The relevant bit of code is below
At the moment its runnig to slow by several orders of magnitude, Working on a 4 by 10 grid it takes about a minute, and I'm going to need it to be able to handle a 50 by 800 grid.
While the code is running, the CPU is not maxed out, so I'm assuming that its the calls to the file system which are taking all the time.
Application.ScreenUpdating = False
Dim ccount As Integer
Dim rcount As Integer
Dim sFilename As String
Dim sPath As String
With ActiveSheet
For ccount = 1 To Range("c1").End(xlToRight).Column - 2
sPath = Left(FileNamesList(ccount), InStr(FileNamesList(ccount), "Pipeline ~") - 1)
sFilename = Right(FileNamesList(ccount), Len(FileNamesList(ccount)) - InStr(FileNamesList(ccount), "Pipeline ~") + 1)
For rcount = 1 To Range("A65536").End(xlUp).Row - 2
by SDB.xls]Pipeline'!$A$1:$AO$300,3,0)
Cells(rcount + 2, ccount + 2) = "=VLOOKUP(""" & Cells(rcount + 2, 1) & """,'" & sPath & "[" & sFilename & "]Pipeline'!$A$1:$AO$300,3,0)"
Next rcount
Next ccount
End With
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()......................
I found this script which is very similar to what I want to do but I am not sure how to finish modifying it.
This script compares every cell in Column B to Z on Sheet1 with Cell A1 on Sheet2, if it matches it will copy the active cell in column A and past the results to column A no Sheet3.
I am the consolidator for a specfic Excel file to be converted into CSV. In order for this file to be uploaded into our database properly it needs to be in a specific format and certain values must be entered if other cells have been filled in. At this point I have locked the formating so users cannot change it and I have created a Macro for blank cell rules.
The Macro is to verify that cells are not blank if the value in A is not blank. At this point it works as it should, for row 4. However, I would like to Loop this Macro throughout the entire worksheet.
The Macro I have created is (minus several columns for simpler reading purposes):
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets("JE FILE").Range("B4").Value = "" And Worksheets("JE FILE").Range("A4").Value "" Then
MsgBox "You must fill in Customer."
Cancel = True
[Code] .......
How do I LOOP the Macro?
Currently, I have two spreadsheets.
The first (SS1) is where my raw data is populated.
The second (SS2) is my template.
In the first spreadsheet, I have a macro (button) that opens up SS2.
Then in SS1, I also have another button that I click and it 'transfers' the first line of data to SS2.
In SS2, I have a save Macro, which basically saves as per the name in Cell A1.
What I would like to do is to loop the process, i.e. when I click the 'Transfer' button in SS1, it will open up the 'template' spreadsheet (SS2), copy the information across, save & close and repeat this for the remaining data in SS1.
Is there a simple coding to use that can loop this process for me?
I would attach the two spreadsheets for you to view but I am unsure how to upload on here.
I would like the macro to loop through rows and highlight the cell in column G if the value is at least 2x greater than the value in the cell in column D.
Right now I have a very long macro... but I'd like to change it so goes through all the rows I assign it to instead of writing many lines of code (lots of if then statements):
Sub Macro4()
Range("G28").Select
If Range("G28") > 2 * Range("D28") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
if i am in an active cell which is empty and want to stop the macro at this point, what code should i type in visual basic to achieve this
View 9 Replies View RelatedI'm trying to get this macro to loop and have no idea what I need to do do to get the macro to keep repeating until it gets to the last item in a list.
I have a list of addresses (some are 3 rows long, some 4 and some 5). And I want to Copy each one then paste special in the empty cell between it & the next address entry.
Here's the code for my simple macro that does one copy & paste ... I have to do each one manually & would like to modify the macro to go all the way to the end of my list.
I read that there are 4 types of loops (While Loop, For Loop, Do While, ??) ... don't know how to choose which one or how to use/format with my exisiting macro.
Sub Transpose_j()
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub..........................
I have record a macro to sort the data on one .csv file .
I have about 1000 of this similiar .csv files, I would like to have a macro loop to repeat the sorting and arrangement .
Workbooks.Open Filename:= _
"G:Data20060616.csv"
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
I'm using a macro to drag down a formula across a worksheet then again further down the worksheet which is working ok but I know there has to be a better way of doing it. At the moment every time I add a new column I have to edit the macro over and over and its getting out of hand. I know there must be a way of rewriting the macro into a loop but my skills are obviously still new.
Here's the macro:
[Code] ......
This continues over to column AO so far then I drop down a few rows and do it again:
[Code] .....
I think I need to set variables that set the row and column each time. maybe a "drag formula one column at a time from row x to row y until column header is empty then move to the new row and repeat"?
I am trying to loop through an action within a macro and require some assistance.
I have the following
I want to loop through all files in a directory and run a macro named "Main" in each file. The file that contain the loop-macro will stay in the same directory as the files I loop through.
View 4 Replies View RelatedI have the following code pasted into Module 1 in my PERSONAL.xls workbook:
View 5 Replies View RelatedI have a number of workbooks which contain 62 tabs each. I need a macro that copies specific information from each tab and pastes it to a summary table which will be uplaoded to an access database. I created a macro that works when I manually click on each tab and run the macro. I then added a loop to try and automate. when I run the macro, it loops through all of the tabs, but it only copies and pastes from the first tab, resulting in 62 line items fron the 1st tab. I need to know how to set the active tab to which ever tab the loop is on.
View 5 Replies View RelatedI have a row of data. The first few columns are given data, and then the next few columns are equations that use the first few columns of data.
The next row down adds the result of a few of the equations in the row above it to the given data, and the equations are calculated again.
It looks a bit like this:
1231437-111213-9
in the first row, 1 and 2 are just given. 3 is just 1+2 (a1+b1), and in the last column, 1 is just 2-1 (b1-a1). In the second row, A2 is A1+C1, and B2 is just B1+D1, C2 and D2 are the same calcs as C1 and D2.
This row is then filled down.
What I would like to do, is just calculate what the first two columns of data are by just entering the number of cycles id like to make, instead of filling down the row.
For example, I would like to just enter N=3 in a cell, and it returns 11 and 2. Or N = 1000 and it would give me whatever the first two values are if i had filled the row down 1000 rows.
I have a table sorted on column A. I need a macro to go row by row on that list and stop when the NAME (column A) is changed, in this example 3 first rows "aa". then I need to define the range of these 3 rows (A2:D4) in the table and make some subroutine. After finishing with this range I need the macro to continue and find the next rows with the same name, define the Range and go to the subroutine (in my example A5:D5)
The next range will be A6:D7 and so on until last range A27:D27. How can it be done?
I tried some CASE, FOR NEXT, DO UNTIL loops to do this but get stuck.
DATA *ABCD1NAMEDATEALERTTEST
2aa10HB3aa20INR4aa41WBC5ww50ELE6zz30DIG7zz61HYD8dd21CRE9dd41PLT10dd60HB11dd71INR1
2rr10WBC13tt20ELE14tt41DIG15yy40HYD16uu51CRE17ii20PLT18ii31HB19ii51INR20ii70WBC21ii80
ELE22oo20DIG23ll11HYD24ll20CRE25ll30PLT26mm21HB27nn30INR