Macro Runs Fast When Rows Added / Deleted Slow When No Rows Added / Deleted

Jun 27, 2013

We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.

Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.

The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.

The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.

We can't figure out why the macro takes longer to run when no changes have been made?

View 8 Replies


ADVERTISEMENT

Summary Sheet Calculate Results Change If Rows Added/deleted In Detail Spreadsheet

Feb 27, 2009

[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.

I'm using dynamic name ranges, as follows, for the detail:

WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)

Other detail data is defined as these examples show:

GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)

All detail data begins in Row 6 in the detail spreadsheet.

In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.

Here are two examples of the calcs I'm using in the summary:

=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")

Can someone tell me what is going on? What I'm doing wrong?

View 8 Replies View Related

Dynamic Named Ranges Where Data Is Manually Added And Deleted

Apr 22, 2013

A lot of the Workbooks that I design for use by myself and colleagues require data to be copied in from external data sources. To avoid named ranges from failing, I always use the following method:

Calculate the length of the data set:

Code:
=COUNTA(INDIRECT("'Data Sheet'!"&"$A:$A"))

(There will be no gaps in the data, hence a count is fine.) This named range is called DSROWCOUNT.

Example named range for the data in column A:

Code:
=OFFSET(INDIRECT("'Data Sheet'!"&"$A$1"),1,0,DSROWCOUNT-1,1)

I use INDIRECT to ensure my named ranges do not fail if the data is deleted (accidentally or intentionally), as #REF! errors will occur.

The problem with this method is that it automatically makes the Workbooks volatile because of the use of OFFSET and INDIRECT, hence the Workbooks always needs to be in manual calculation mode to be usable.

View 7 Replies View Related

Use A Named Range To Many Columns Are Added Or Deleted The Data Is Pulled Correctly

May 4, 2006

I have a spread sheet that I pull data from different columns on a particular row. The problem is the code I used works great as long as the column never moves from its current location. Is there a way to use a named range to make the following piece of code work, so no matter how many columns are added or deleted the data is pulled correctly?

View 2 Replies View Related

Display Details Of Rows Deleted In Macro

Nov 25, 2006

I have a macro that deletes certain rows. I'd like the macro to tell me what it deleted specifically (if anything). Here's some of the macro:

For rownum = 1 To maxrownum

item = Cells(rownum, 1).Value
If item = "PUBS" Or item = "SWCDROM" Or item = "PC PACK" Then cells(rownum, 1).EntireRow.Delete

How do I get the macro to tell me if it deleted PUBS, SWCDROM, PC PACK, all three or nothing at all?

View 9 Replies View Related

Macro That Will Calculate Sum Of Cells Even If Rows Are Added

Feb 12, 2014

So I'm trying to create a macro that will simplify dealing with a file we receive at work pretty often. I've enclosed a spreadsheet that shows a very basic example of the files we receive. What I need to happen is this: On the rows that have only the Company # in column A and Total $ in column D (ie. rows 5 and 6), I need the Total $ amount moved up one row and then for the row it was previously in to be deleted (at that point that row should only have the Company $ and no other data in it so it's basically useless). The problem I'm having is that if any rows are added to the file (for instance if an row was added between rows four and five) it would throw off my macro.

View 3 Replies View Related

How To Automatically Update A Macro To Adjust For Rows Being Added

Mar 5, 2013

I have a worksheet used for a car game. Each row is designated to show one particular car that can be used in the game, so let's say that there are 40 cars taking up 40 rows.

Column A shows the car type and model; column B shows the engine that the cars has represented by a number (the higher the number the better the engine is); and so it continues, Column C shows the transmission; column D the brakes etc.

Underneath the 40 rows (lets say from row 45 to 65) I then have a table with spare-parts that can be used to replace the cars existing parts. This Parts-table is also arranged with engines in column B, transmissions in column C, brakes in column D etc...

This point of this layout is that it should be easy to compare each type of part in the spare-parts table with the corresponding type of part already used in any of the cars as everything is perfectly lined up by the columns.

About the spare-parts table: because the inventory in the spare-parts table often are added to or subtracted from, as parts are won in races or being put on the cars, I have created a macro to sort the parts in each column so as to put the best parts at the top of the table and the bad ones towards the bottom - so far so good.

The problem is this - as I add new cars, the table of spare-parts is pushed down worksheet and that means that the macro no longer will reference the right rows. So the question is this, how do I automatically update the macro to adjust for rows being added above it? I am not really interested in redesigning the worksheet.

View 2 Replies View Related

VB Code To Move Rows Up Once Row Above Deleted

Jun 2, 2013

I have this code that removes a group of cells on any row that contains "Y" in Column L, but it does not move the remaining group of cells on a row up after the cells are deleted.

VB code so that when a range of cells are deleted, the row(s) below are moved up?

Sub RemoveReceived()
LastRow = Cells(Rows.Count, "H").End(xlUp).Row 'change "H" to the column that determines which is the last row
For x = LastRow To 4 Step -1 'assuming it starts from row 4
If Cells(x, "L") = "Y" Then
Range("H" & x & ":K" & x).ClearContents ' or use Delete

[Code] ........

View 2 Replies View Related

Find Details Of Deleted Rows

Feb 3, 2009

In excel, is there anyway to find the details(history) of the previously deleted rows (using vba or anyother way)?

View 9 Replies View Related

Copy All The Rows Until Row 37 And Move It One Row Up After The Selected Row Is Deleted

May 1, 2009

enables the user to select a row to delete. However, I need to do more than that. I need to be able to copy all the rows until row 37 and move it one row up after the selected row is deleted. Below is the code

View 3 Replies View Related

Add Formula To Added Rows

Oct 23, 2006

How do I apply a formula to every cell in a given column?

I'd like my workbook to store this formula:
=c<current_row> + d<current_row>
in every cell of column P as a default.

This means that even if I do "insert row", I wouldn't have to copy this formula into the new cell.

View 6 Replies View Related

SUM Account For Added Rows

Jan 4, 2007

I've created a simple budget sheet outlining various group expenses eg: Fixed, Flexible, Variable, and their totals sums.

How can I add all totals while still allowing for a new row to be inserted in any group?

eg:..............Row E

1........Fixed Expense group
2..................$20.00
3..................$40.00
4..................$40.00
5....Total sum $100.00
6.......Flexible Expenses group
7...................$10.00
8...................$20.00
9...................$20.00
10..................$10.00
11..................$50.00
12...Total sum $110.00
13......Variable expenses group
14..................$10.00
15..................$10.00
16..................$10.00
17..................$20.00
18..................$10.00
19....Total sum $60.00
20--------------------
21..Grand Total $270.00

View 4 Replies View Related

Format Range When New Rows Added

Jul 24, 2009

I have a worksheet on which users will enter data one row at a time, in columns A-K. When the user starts to enter data in a new row #, that is, when they make any column in the next, unused row have non-blank value, I'd like the sheet to update the borders of the row for columns A-K. I want the Range from A1 to K# to have full borders, so that the whole data set is outlined and easier to read.

For a bonus, I'd like the formulas from the previous H, I, and J cells copied down into H#, I#, and J#, adjusted appropriately for their reference changes.

Normally, I would work around the edges of such a VB problem by recording the actions manually and then modifying that code as I learned more. But I'm not sure if what I've done is the right way to start that process. I think that if I use the CurrentRegion property, and the Worksheet_Change event, I'll be well on my way.

View 2 Replies View Related

Moving Rows When New Information Is Added

May 18, 2012

I have a workbook where on one sheet various peoples information is listed and based on their organization the information is then linked to one of a few different worksheets for costs. On the cost worksheets, when a new person is added to the original worksheet, their information populates the next row. At the end of the cost columns I have a the total cost of each person added together. What I need to do is have these totals continue to move down the worksheet as new people are added. Is there a way to do this in excel using VBA or another function?

View 1 Replies View Related

Formatting New Rows Added Automatically

Dec 10, 2009

I am working on a spreadhseet with multiple tables present on the sheet. Each table has various formatting, merged cells, formulas, and defined lists. I need to have the table add a new row below the last including cell formatting, formulas, defined lists, so I can just keep going through and do data entry. The code adds a row at the end so I don't run into the table below the present one, by monitoring how far away it is from the "Type" cell tag in column A. The code below is a compilation from viewing other threads, but it just does a copy and paste, including the data that was input so I'm looking at a duplicate row. How can I get ito to clear values in the new row? I can't seem to get it to paste xlPasteFormat and xlPasteFormulas
Is the new row in fact being added below the last? I think it's up one after the macro had run through.

Private Sub Worksheet_Change(ByVal Target As Range)
r = Target.Row
c = Target.Column
If c <> 1 Then Exit Sub
Application.EnableEvents = False
NextLineValue = Cells(r + 3, c)
If NextLineValue = "Type" Then
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).EntireRow.Copy
ActiveCell.Offset(0, 0).EntireRow.PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If
Application.EnableEvents = True

End Sub

View 5 Replies View Related

Extend Formula When Rows Added

Jan 5, 2007

I have a sheet (Sheet 3) that is pre-populated from another sheet (Sheet 1), and the user is allowed to update the numbers (for forecasting).

I have a column ( Total Hours) that I use to total the new numbers in the row.

If the user inserts a row, the Total Hours formula does not follow. This is the Total Hours formula that I am using SUM Formula
(=IF(SUM($G30:$AP30)=0,"",SUM($G30:$AP30))

How do I (Can I) get the formula to cascade into the new row.

View 8 Replies View Related

Unchanging Sheet Reference When Rows Are Added

Oct 28, 2009

I have a sheet titled PartNumbers....which contains in numerical order just 1 column of random 1, 2, or 3 character numerical part numbers. nothing special. and not necessarily in 1 by 1 order. like 45,46,47,48,49, it will skip number more like 45, 49, 50, 55. I have another sheet titled FinalReport.....that takes the raw data i have from the PartNumbers sheet and outputs the data onto a nice pretty looking designed sheet.

on 1 column of FinalReport I have =PartNumber!C2, C3, C4 and so on for the total number of parts i have to fill in the FinalReport sheet for each cell. The Question is, on PartNumbers when I want to add a part, say my sheet goes number 45 then 49 and i want 46... What I do is right click row number and select Insert, that adds a new row for my new part number. Now that move doesn't correspond to FinalReport now, it just removes the row i inserted. How do I get everything to flow to FinalReport?

View 5 Replies View Related

Automatically Adding Rows When Worksheets Are Added

Jun 30, 2014

I'm using a workbook that has one sheet that pulls data from all the others, displaying a series of rows that summarizes data from each worksheet, each of which is full of data. Its a worksheet that a lot of people use so its kind of tedious to use as of now, because whenever you add a new worksheet of data you have to manually create a row and then manually change all the worksheet references. Is there a way to make rows automatically add and update when a new worksheet is added? I know it would probably have involve some sort of Macro or VBA.

View 1 Replies View Related

Sum Do Not Include Rows Added After The Predefined Sum Range

Apr 16, 2008

I'm looking for: a cell (in column C) that sums Column C from C6 to the row above said cell. (A Total) So if new rows were added, these values would be included in the sum.

What I'm currently working with is a simple Sum formula, but this sum does not include rows added after the predefined sum range.

View 9 Replies View Related

Adjust Formula Range Reference As Columns/Rows Added

Apr 4, 2008

I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:

=( SUMPRODUCT(((Workings!$H$3:$H$1000=Explanation!B9)+(Workings!$E$3:$E$1000="Buy")*(Workings!$J$3:$J$1000))))-998

becomes
=(SUMPRODUCT(((Workings!$H$511:$H$1508=Explanation!B10)+(Workings!$E$511:$E$1508="Buy")*(Workings!$J$511:$J$1508))))-998

View 3 Replies View Related

Fast Macro To Hide Empty Rows

Mar 22, 2014

Im looking for a macro that hide empty rows. I found some simple macro but that are long to execute. While looking for a faster code, I found two codes that work pretty fast. But as I don't understand VBA I am not able to adjust them to my situation.

First macro: I am able to specify my range (B6:B77), but the macro applies to blank cells and I need to apply to "" cells.

[Code] .....

Second macro: very fast as well. Here, it applies to "" cells, but I am unable to specify a range. So the rows 1 to 4, which are empty, are hidden but should not.

[Code] .....

View 11 Replies View Related

Run Macro When Worksheet Is Deleted

Feb 26, 2009

I have a macro i would like to run whenever a user deletes a worksheet...

I would like the user to be able click as normal to delete,(ie. right clicking the tab and selecting delete worksheet, or selecting delete worksheet from the menu) but would like to run my macro when they select delete.

I am using excel 07 if that helps, or i could use another version...

My macro will save a copy of the deleted worksheet as xlveryhidden as a backup..

View 9 Replies View Related

Macro: Deleted Record From Columns

Aug 21, 2009

Complete List of People in Column AA.
Partial lists of these same people in columns A, C, E, G, I, K.
Goal: Once I put that persons name in A, C, E, G, I, K, I would like it to be deleted from Column AA.

View 9 Replies View Related

Macro Resets Total In Text Box If A Number Deleted?

Jun 26, 2014

Attached file 140625 box adds.xlsm has three textboxes that are totalled in the fourth box.

The Code updates the total automatically as you ENTER numbers into cells, and overcomes "blanks", but I need it to update the total if you delete any of the three numbers.,

As shown below, I set the Code to "Exit Sub" if a cell becomes blank, but can't see how to invert it without going through the same "checking" process twice for the other cells.

As the Code structure is essentially the same for each box, I've just shown the first one here:

VB:
Private Sub TextBox1_Change()
If TextBox1.Value = "" Then Exit Sub [code]....

View 1 Replies View Related

Macro Runs Slow

Nov 13, 2008

The 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...................

View 9 Replies View Related

Macro Disable/enable Still Appears After Macro Deleted From Workbook

Oct 3, 2006

I have a question concerning Macros and the Disable/Enable prompt. My boss has a spreadsheet which has been used for years and he recently wanted to and did remove the macros from the spreadsheet (they were no longer necessary), but the disable/enable prompt still appears when the spreadsheet is opened. I replicated this in a test spreadsheet with a simple insert line macro and received the same results. Is there a way to remove the macros and the disable/enable prompt once they are removed? I know about setting the security to low to not see the prompt, but I would think that once the macros are deleted, the prompt should not appear any more.

View 2 Replies View Related

VBA / How To Spin Chart Very Fast Then Slow And Stop

Nov 20, 2013

I want to make a prize wheel (aka Wheel of fortune) I try to follow this code >> wheel of fortune but I stuck at the animation in order to make it look like the real prize wheel. as far as I google, this youtube (Simple Excel Pie Chart turned into AMAZING Electronic Prize Wheel of Fortune! - YouTube) demonstrate the result that I really want my chart would be.

how to make the prize wheel spin very fast at first and slower later until stop?

View 3 Replies View Related

Password Protect Added To Macro

Sep 10, 2009

I have recorded the following macro and wish to add unprotect (with password), run the macro - which formats the worksheet and then password protect all cells with the exception of range E15:H413 which I want to leave editable by users.

View 9 Replies View Related

Run Macro Code When New Sheet Added

Apr 2, 2008

I have code in the NewSheet event of ThisWorkbook which tracks new sheets being added. But when a worksheet is added by copying an existing worksheet this event doesn't seem to be triggered. Buy logically a new sheet has been added to the workbook so the event should be triggered. Is this a design flaw or am I missing something?

View 7 Replies View Related

Autofill Summary Macro As New Worksheets Are Added

Jun 11, 2014

Trying to write a macro so that my summary worksheet will auto populate when new sheets are added and filled out. I want the PO#, Quanity, Date ordered, Vendor, Subtotal, Tax and Total all to transfer from worksheets like F001 to the summary sheet. Yes there is only one F001 sheet filled out but that number will rise into the hundreds as the project progresses. No idea where to begin when writing this macro.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved