File Unstable If It Gets To Big: Only Deletes The First Entry
Jun 28, 2006
The file that I am currently working on is getting really big. It is currently 101MB and we aren't done yet. In contains a lot of Macros and also a lot of ordinary excel formulas. I have recently noticed that excel sort of gets unstable with this file. For instance if I select a very large area eg A200:IK19000 and delete it, it only deletes the first entry (A200). What is going on? Is this a typical excel thing? What can I do to fix it or avoid it in the future?
I have a question about linking between Workbooks. I have 2 Workbooks and some cells have Formulas targeting each other, formulas like IF, VLOOKUP... Etc...
I have it that way because the users cannot access other user whole information, even though there is no need of real time sync there is a need to share some of the information between the users.
So.. some cells with the formula appear as #REF, I
I have a database file with a list of components. Each component has manufacturers name, part number, description etc. I need to create an individual file from each of the components in the list. I would like a quick and easy way to copy the information in each Row and generate a new file for each Row containing the existing template I have.
would like to save a spreadsheet with the contents of a cell as the name. My problem is that I have several macros in the workbook and if I change the name of the file when I save, it won't run the macros when I reopen because the filename has changed. It presents me with an error saying it can't find the macro.
I want to be able to get a value from a specific cell in another workbook, the problem i have is that i want to be able to select what file the active workbook looks for from the start page of my current workbook. For example when i load the workbook i get my startup page ( sheet 1) where i will be able to select what external workbook the active workbook will be linking with, i will then link the active workbook to the value in a specific cell from the selected external workbook. I know to link to another workbook i would use ='[filename]sheet!cell
However i want to be able pull up a 'open file dialog box' which i select a file name which will be used as 'filename' in the formula above.
I work on an Excel file to determine property date of entry. While I have not completely worked out all of the bugs or the permutations or problems that may come up, I do feel that I have discerned an important part of the problem. The sheet where I set up my formulas and functions has the name 410a elig and coverage and should have a green tab.
To explain my reasoning; in this hypothetical situation, a company's plan has two dates of entry, January 1st and July 1st. An employee enters the plan on which of these two days follows closest or coincides with his or her attainment of 1,000 hours of service during the twelve months forming his or her initial eligibly computation period. If an employee starts work from January 2, 2005 to July 1, 2005, then that employee would enter the plan (provided he or she worked 1,000 hours during the initial eligibility computation period) on July 1st, 2006. If that employee started work from July 2, 2005 to December 31st 2005, then that person would enter the plan on January 1st, 2007.............
Some of the abbreviations or acronyms spelled out: OYFDOH=One Year From Date of Hire; for now, the issue of leap years I will set aside Month Function Month=Month Function JJ: January or July
I need a macro that searches my spreadsheet for a keyword in Column B. If it finds the keywords (or an array of keywords would be even better), it then deletes the entire row above the keyword, the row with the keyword, and the row below the keyword.
If the macro finds the text in column 2, it would delete 1, 2 and 3.
I need to have a second macro that performs a very similar function. it copies the 3 rows (similar to the example below) to a different sheet based on the keyword.
deletes a row if it finds a specified value in a specified column (in this instance, "NB" in column E). However, it is very slow and some end users are complaining about the amount of time it takes to run. Here's what I'm using at the moment:
I’ve got a macro that automatically starts when the workbook is open. The macro finishes with
Application.Dialogs(xlDialogSaveAs).Show
The document will be always saved with a new name. As I just need the macro once when the workbook is created I’m looking for a code that deletes the macro before the workbook is saved. I tried to record a macro while I was deleting another macro, but apparently it is not working (the recorded macro is empty). Does anyone know if there exists a code to delete a macro?
i have this macro made by another member here that looks for "T" in column 10 and deletes entire row if in that row has a "T". I need it modified to also delete if it contains "FT" and also erase if cells is blank.
Sub Macro8() Sheets("FORMERS").Select 'deletes entire rows based on a text on one row (this case errases all that have a "T" in column 10 (column J)
Dim DeleteValue1 As String Dim rng1 As Range Dim calcmode1 As Long With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With...........................................
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.................................
I have a macro that that stops every time I’m trying to delete the sheet. I get this message. “Data may exist in the sheet(s) selected for deletion. To permanently delete the data, prese Delete.” and I have to manually click “delete” to continue running the macro. How can I avoid this stop so that macro runs thru it without stops?
I need help writing a macro. I only have experience recording them, not writing them in VBA.
I need to check columns A and B for blanks. If A and B are both blank, I want the macro to delete the row and move on to check the next row. I need to perform this macro for the entire worksheet.
I need a Macro that deletes all cells in column D of all worksheets in a book that are equal to the word "Timeout" and to shift the cells to the right of the "Timeout" cells into the deleted cells but leaving all other rows where the word "Timeout" is not present.
So in the case of Rows 2, 4, 6 and 8 containg the word "Timeout" in column D, I would like cells D2, D4, D6 and D8 deleted and the remaining cells shifted to the left but cells D1, D3, D5 and D7 left as they are.
Dim rng As Range Dim i As Integer, counter As Integer
Set rng = Range("1:1")
i = 1
For counter = 1 To rng.Columns.Count
'If cell i in the range contains an "x", 'delete the column If rng.Cells(i) = "x" Then rng.Cells(i).EntireColumn.Delete Else i = i + 1 End If
Next
My problem is that I have cells in other worksheets linked to the worksheet that is running this macro and everytime I run it, I get a handful of "#REF" errors. I think this problem might be solved if I could simply delete the contents of the column rather than deleting the entire column. How can I modify my code to do this?
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...................
Below is an extensive macro that basically writes data from a user's template to a certain data sheet (in the form of rows) depending on their "service group." There have been 3 separate instances of a user saving data and instead of adding rows onto the bottom of the entire list of data, the sheet only has their data and no headers/filters. I believe it is somehow deleting all the previously written data that was on that sheet.
I should also mention that this is a shared workbook. I specifically created separate sheets for each "service group" to eliminate the possibility of users overwriting each other's data (Perviously 60+ users were saving to the same sheet and running into conflicting changes errors. Now the max users writing to one sheet is 10-12). Since it is not occurring every time, I am not sure where the error is. Could it possibly be in the sort or delete sections in bold below?
I am using Excel 2003.
Code: Sub SaveData_Test() '--------------------------------------------------------------------------------------- ' Procedure : SaveData_Test ' Author : Julie/Cecil ' Date : Fri, 1/13/12 ' Purpose : Modified Julies code to capture date entries ' associated with hours logged per category. ' Check notes attached to "sOp" string comment ' for additional details.
how to put together a macro for a command button that deletes rows with certain text in a docyment - which worked fine a week or so ago - but now the macro will not work.
I made changes to the document - but made sure i also made changes to the code.
I am trying to do here is select a value from a drop down which is linked to a formula which triggers the macro shape..i have all that down already... but the problem is that I will have multiple values in the same drop down and I needed to figure a way to delete the previous macro shape in that range. And so i have created the delete all shapes code below but it seems whenever I use the code it deletes the data validation drop downs
here is what I have so far...
Function Macro() ' DELETEALLSHAPES ' ' ActiveSheet.Shapes.AddShape(msoShapeRectangle, 220.5, 105.75, 92.25, 51#). _ Select End Function
I am having a sheet where I keep track of when online surveys have been sent to users. The users enail address (column K) may be on the list for several times, but I need to make sure that there is at least a 7 days pause between sending the first mail and the second, depending on the visit date (column G).If there are less than 7 days between two entries with the same email address, the user is not qualified for taking another survey.
I was thinking to write an IF formula which returns either 1 or 0 and then let conditional formatting highlight and HIDE the row via a VBA loop.
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
I used a form with textboxes for data input for one of my vb macros. Currently I have the private sub from the form transfer those entries to a remote cell on the spreadsheet, (like in column "HZ"), so that the macro that will actually utilize them can retrieve them. Is there a way to pass that data directly from what is entered in the form in the textboxes to the macro that will actually use them?
This is weird - if you delete a sheet that contained a control then
a. showing a modeless userform resluts in a userofrm that goes invisible at subroutine End b. public variables lose their value
These things do not happen if the sheet did not contain a control. Attached is an example file - put the inputfile.xls in your default file location (or add a path in the code) then open the ProblemDemo.xls and run the main macro to see it fal - isthis another Excelbug I've found?
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
