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?
View 2 Replies
ADVERTISEMENT
May 13, 2013
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
View 2 Replies
View Related
Jul 8, 2008
I seem to be having problems with using relative RC in vba within an automated reporting spreadsheet..
in my 'left lookup' below.. the R seems to point to row 1 no matter what row i am writing to..
View 11 Replies
View Related
Aug 19, 2009
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.
View 9 Replies
View Related
Apr 2, 2009
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.
View 9 Replies
View Related
Feb 1, 2008
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.
View 2 Replies
View Related
Dec 31, 2008
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
OYFDOH MON JJ Year
8/7/2006 8 1 2007
1/2/2006 1 7 2006
6/1/2006 6 7 2006
3/1/2006 3 7 2006
8/1/2005 8 1 2006
6/1/2006 6 7 2006
View 5 Replies
View Related
Jun 30, 2008
I am making a macro that deletes #N/A's, but i want to add a few things to it
that should be deleted:
the phrases "total board", "total metal" and "Item", and any non-text (i.e. any
number) that appears.
this is what i have so far:
View 9 Replies
View Related
Nov 3, 2008
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.
So for example,
(Column B)
1 - Row Above
2 - Keyword Row
3 - Row Below
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.
View 3 Replies
View Related
Dec 23, 2009
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:
View 5 Replies
View Related
Aug 11, 2006
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?
View 5 Replies
View Related
Mar 27, 2014
What each piece of this code means and what it is doing? The more details the better
[Code] .....
View 1 Replies
View Related
Mar 25, 2013
I have entries that look like this - 300-7188258-1
300-8983980-5
I need to remove the dashes. I find and replace all - with nothing. that has worked for YEARS. Seems simple to me.
Today, when I do it, Excel leaves the "300" and removes everything else. I even did a CLEAN function and it still does it.
View 3 Replies
View Related
Aug 20, 2008
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...........................................
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
Oct 15, 2008
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?
View 2 Replies
View Related
Dec 11, 2008
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.
View 8 Replies
View Related
May 22, 2007
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.
Here is an example line of data:
16:02:30LEXKRunningprocedure:125sth
16:32:30LEXKRunningTimeoutprocedure:X125sth
17:20:54LSXPRunningprocedure:oxford
17:56:48LSXPRunningprocedure:xoxford
18:48:24LEXKRunningprocedure:125sth
19:18:24LEXKRunningTimeoutprocedure:X125sth
View 9 Replies
View Related
Aug 5, 2009
I'm using the following code to delete columns:
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?
View 4 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
Oct 17, 2006
I think the loop is deleting my previous entries and only putting the last results in.
For assortedrowindex = 3 To 400
targetdate = Date
Do While Month(targetdate) = Month(Date)
Redim Preserve arrTransactions(assortedrowindex - 2)
arrTransactions(assortedrowindex - 2).CUSIP = Cells(assortedrowindex, 12)
arrTransactions(assortedrowindex - 2).OrderDate = Cells(assortedrowindex, 9)
arrTransactions(assortedrowindex - 2).BuyCurncy = Cells(assortedrowindex, 2)
arrTransactions(assortedrowindex - 2).SelCuurncy = Cells(assortedrowindex, 4)
arrTransactions(assortedrowindex - 2).Fund = Cells(assortedrowindex, 7)
arrTransactions(assortedrowindex - 2).SettleDate = Cells(assortedrowindex, 10)
arrTransactions(assortedrowindex - 2).BuyUnits = Cells(assortedrowindex, 15)
arrTransactions(assortedrowindex - 2).FxRate = Cells(assortedrowindex, 16)
If targetdate < arrTransactions(assortedrowindex - 2).SettleDate Then
' Sheets("Sheet2").Activate...............................
View 4 Replies
View Related
Jan 19, 2012
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.
[Code] ..........
View 5 Replies
View Related
Mar 20, 2014
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
View 4 Replies
View Related
Feb 4, 2009
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.
View 2 Replies
View Related
Jun 1, 2009
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
Function CIRCLES()
'
DELETEALLSHAPES
'
'
View 9 Replies
View Related
Sep 11, 2013
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.
View 3 Replies
View Related
Mar 14, 2014
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.
View 3 Replies
View Related
May 1, 2009
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?
View 4 Replies
View Related
Jun 15, 2009
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?
View 9 Replies
View Related
May 26, 2009
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
View 6 Replies
View Related
