Run-time Error 13 On Deleting Rows
Jan 21, 2009
I'm not the sharpest tool in the shed when it comes to excel programming but I manage. However, I can't seem to get rid of this run-time error 13. Could anyone take a quick peek at the document and spot the problem?
My intention is this, when typing 'Yes' in the AB column on a certain row, that row is cut from that worksheet and moved to the 'Archive' worksheet, also, simultaneously, I want to send a mail to a specific address.
Here is when I run into the error, I suspect when the row is removed with the 'Yes', those two macros clash.
View 6 Replies
ADVERTISEMENT
Jun 9, 2009
I have a macro which is copying data from several worksheets into one consolidation worksheet. When determining where to paste the data into the consolidation sheet, the macro includes some logic to find the last row that has data in it (using e.Range("A65536").End(xlUp).Row, where "e" is a variable holding the name of the consolidation worksheet).
Once all the data is on the consolidation worksheet, I have a second worksheet with formulas that link to the consolidation sheet. The issue I have is that the first step of my consolidation macro deletes all data on the consolidation sheet to ensure that no data is double-counted). I am deleting the data with logic that simply deletes all rows from 3 to 65536. Once these rows are deleted, Excel returns a #REF! error on my second worksheet which is linking back to this data.
Rather than deleting the rows on the consolidation sheet, I have tried using the Clear and/or ClearContents commands instead. This works (i.e., my formulas no longer error out), but results in the consolidation macro running very slowly (~15 minutes, compared to
View 9 Replies
View Related
Mar 18, 2008
I have a form on a SheetA wherein all cells are referencing the data in cells on SheetB. When I delete or add a row on SheetB, the cells in that row on SheetA read REF#. I need the row in SheetA to be added when I add it in SheetB and vice versa for when I delete a row.
View 5 Replies
View Related
Mar 12, 2014
A large Excel file that I have been working with for months is now giving me trouble.
When I tried to delete 2 rows, it displayed a message: "Excel cannot complete this task with avail resources. Choose less data or close other applications."
I opened a previously saved version of the file and tried deleting rows, and the same message came up.
I shut down the computer, rebooted, same problem.
Any way to determine if a file has been corrupted .... or even worse ... is there a possibility that my Excel program itself is corrupted?
View 8 Replies
View Related
Jul 13, 2009
I have this code that I have had for a while.. it works okay on my computer and does as intended... it deleted all empty rows in the selected range after checking to see if any cells contain anything that makes it look blank but isn't (it cleans those cells).
So on my computer it works... on a coworkers computer it converts every used cell in the usedrange to #VALUE...
View 14 Replies
View Related
Feb 24, 2014
I'm trying to use the code below to copy active sheet into a new workbook and clean some parts of it so it will be ready for next department's data input work. But when it comes to deleting empty rows in it, code gives Run-time error "1004" : Delete method of Range class failed error and it marks the part "Rows(r).Delete"
[Code] .....
View 8 Replies
View Related
Feb 14, 2013
I want to establish a link from my worksheets. Using the conventional link method I was able to link the values from my first worksheet to the second worksheet. My problem is when I delete a particular row. The reference of the second worksheet will have an error #REF! since I deleted those cells. Is there any way that I can link my two worksheets without any error that even if I deleted a particular cell/row the reference is still intact?
View 2 Replies
View Related
Mar 19, 2014
Below is an example of an Excel sheet I'm working with: [URL]
Basically, I'm trying to delete the duplicate rows by matching ID, Date and Type. If ID, Date and Type are the same, then, I want to only keep the record with the earliest Time in case of Type = In and the latest Time in case of Type = Out.
So, for example, in the case of ID = 1, there are 3 records for In, I only want to keep the one where Time is: 8:01 as this is the earliest. The other 2 records should be deleted.
Similarly, in the case of ID 3, I want to keep the record where Time = 18:05 as this is the later time out of the 2.
Can this be achieved by Conditional Formatting or Macro or VBA?
View 1 Replies
View Related
May 20, 2013
The code below works nicely to insert a time stamp in column B when the user selects "Yes" in the corresponding cell in column A. The problem is if I try to delete any rows or clear the contents of the cells I receive a "Run-time Error 13 - Type mismatch." If I delete the contents of the cells one at a time I do not receive the error. What I am trying to do is the user has a list of items to select from in column A. Only if "Yes" is selected I want the time stamp in column B to appear.
Code is below.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Value "Yes" Then Exit Sub
If Not Intersect(Range("A1:A50"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 1)
[Code] ......
View 6 Replies
View Related
Mar 3, 2008
I am working on a sales sheet for my business. I have a worksheet that has the names of everyone in my store that has sold anything in column A. I want to create a list that has just my full time sales people and will delete everyone else.
View 9 Replies
View Related
Sep 27, 2009
Run time error 1004 when adding rows. I have the following code, called from a Userform:
View 4 Replies
View Related
Apr 3, 2008
I'm trying to run this macro and I keep getting the "Run-time error '1004' Application-defined or object-defined error"
I am just trying to hide the rows that do not contain anything in them with this
x = 2
Sheets("sheet2").select
If Cells(x,1).value = "" Then
Rows("x:x").select
Selection.EntireRow.Hidden = True
Else
x = x +1
End if
The line that is giving me a problem is "Rows("x:x").select". but when i change x to an actual number, it works. I cannot have a definitive number because where there is data will vary every time this is ran.
View 9 Replies
View Related
Jan 10, 2009
Here is my table which is just a test sample of the larger table, but in the end, it is column 1 I want to base the new ws on.
Before Macro
AY *AB1UnitCount2533210431 LGR SQ10584631 LGR SQ7726 Excel tables to the web >> Excel Jeanie HTML 4
The code below works fine through the first instance of the match and adds a new ws based on the name, but when it gets to the second match the macro tries to add the ws all over again and I get a run time error 1004 which states you can not add a ws and name it the same as one that already exist. I only have one sheet in my wb titled "AY". How can I also have the two column headers transfer to the new ws?
Public Sub CopyUnit()
Dim N As String
Dim i As Long
Dim ws As Worksheet
Set ws = Sheets("AY")
N = Worksheets("PAS Codes").Range("L14").Value
For i = Range("A65334").End(xlUp).Row To 1 Step -1
With ws
If Cells(i, 1).Value = N Then
.Rows(i).Copy
Sheets.Add.Name = N
Rows("1:1").Select
ActiveSheet.Paste
End If
End With
Next i
Application.CutCopyMode = False
End Sub
After Macro
31 LGR SQ *AB1UnitCount231 LGR SQ7331 LGR SQ10 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Sep 25, 2012
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies
View Related
Nov 6, 2008
How do you generate an error message that states "You are not authorized to delete this worksheet" any time a user attempts to delete the specified worksheet? This message also has to block the user for deleting the worksheet.
View 6 Replies
View Related
May 25, 2013
The first code hides everything just fine based on the date in A1. When I change it to the second code to delete instead of hide it is leaving a bunch of rows that the 1st code hides. Both codes have the same search criteria.
Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Hidden = True
Next cell
Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Delete
Next cell
View 4 Replies
View Related
Sep 30, 2008
I have the following codes to delete all blank rows in column A
Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow
With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Rows(t).Delete
End If
Next t
End With
End Sub
Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.
View 9 Replies
View Related
Feb 3, 2007
I have a macro (below) that takes a CSV File and creates multiple worksheets and then filters specific records out of each newly created sheet. The creation of the sheets works fine. But, after setting the filter and then deleting the selected records, when I try to 'Show All', which should leave the unfiltered records, I get an error indicating something is wrong with the 'ShowAllData' method.
Here is the macro up to the point where the error occurs: ...
View 6 Replies
View Related
Apr 11, 2007
I have used the code that was previously suggested in this forum for deleting a module. I have module 2 looping into module 1.At the end of module 1
I tell it to delete itself,likewise for module 2.
I placed in the "this workbook" on open event, the code
Application.Run" CALENDAR.xls!TEST
In the "this workbook" I have also put code in the "before close" event:
If Me. Saved=False Then Me.Save
All is great until the end user reopens the workbook and it locks up on the workbook open event because there is no modules to run now.
View 6 Replies
View Related
Oct 8, 2008
I have written a Excel (2003) that searches a worksheet for a string in any cell. If the string is not found, it uses the 'On Error GoTo' command to jump to a given label. It works fine on the first string not found. When it searches for the next non-existent string, it fails with:
'Run-time error '91':
Object variable or With block variable not set'
Do I have to clear a buffer after each cells.find search?
My
View 7 Replies
View Related
Sep 8, 2006
I have users that submit a workbook quarterly. Many of the submissions contain name ranges that I need to remove. I wrote the below to delete the named ranges but encounter an error when the macro encounters a named range with the Kanji (Japanese) character set. How can I update this to delete these ranges as well?
Sub DeleteNamedRanges()
Dim ws As Worksheet
Dim NR As Name
On Error Goto ErrTrap
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
For Each NR In ActiveWorkbook.Names
NR.Delete
Next
Next
ErrTrap:
If Err.Number = "1004" Then
Resume Next
End If
End Sub
View 9 Replies
View Related
Jan 9, 2008
I got a code to delete all rows in the sheet which contain the word "DETAILS" but I now want to delete all the rows that do not contain the word "DETAILS"
My code if needed is:
Sub Find_details()
Dim rng As Range
Dim what As String
what = "DETAILS"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub
View 9 Replies
View Related
Jun 27, 2014
I have a macro which creates and names worksheets. I am making a button which also deletes the latest of these created worksheets, but doesn't delete other sheets. I am getting the error: Run-time error '424': Object Required. Here is my code for deleting the sheet:
[Code] .....
MSCount stores the highest "MS#" sheet.
The first line of the IF statement is where the error is.
View 7 Replies
View Related
Oct 10, 2006
I'm not sure why this is happening, but every other time I run this one specific macro, I get a "Run-time error '1004': Paste method of Worksheet class failed". I even tried running this macro, then running a different one, then running this again, but I still got the error every other time.
Every time I get the error, it highlights this line of
Sheets("Regenerate Request").Paste
This is all of the code up to where I get the error:
Sub YesRegen()
' after user has hit Yes on the RegenerateRequest macro, this posts the new request to
' the log, generates the new file and attaches it to an email
Application.Run "LogUnprotect"
Range(Range("A" & ActiveCell.Row), Range("K" & ActiveCell.Row)).Copy
'Selection.Copy
Sheets("Regenerate Request").Activate
Application.Run "RegenFormUnprotect"
Range("A40:K40").Select
Range("A40").Activate
Sheets("Regenerate Request").Paste
View 10 Replies
View Related
Dec 11, 2009
Say I have a method that iterates through a bunch of Sheets. I check the name of every sheet, if it starts with "Data", I need to make everything between A4 and AZ500 empty (either by clearing cells or deleting rows doesn't matter how, as long as the result is an empty sheet below A4).
View 2 Replies
View Related
Sep 21, 2009
I have an excel spreadsheet that contains about 1000 rows and about 25 columns. The file contains employee information, name, id, cost center, department, title, FTE...etc. Column E contains the cost center which is a 7 digit number (i.e. 8001234). Within the 1000 rows of data there somewhere to 70 cost centers. I would like to delete all rows where a cost center does not match a list of 13 cost centers. I'm thinking I need some kind of array where I type in the 13 cost centers in the code, but I'm unsure of the syntax within VBA.
Also, the second step of the code I'm looking for is to delete all columns except for Column B, E, J and L. Those, by the way are Name, Cost Center, Job Title and FTE.
View 2 Replies
View Related
Oct 26, 2011
I want to delete all rows in the column of the ActiveCell when the ActiveCell.value < 0.01. Could you tell me why the code below doesn't work? It deletes the right rows but keeps looping without stopping
Sub DeleteZeros()
Dim Col As Long
Dim StopRow As Long
Col = ActiveCell.Column
StopRow = Cells(Rows.Count, Col).End(xlUp).Row
Range("A1").Select
[Code] .........
View 6 Replies
View Related
Apr 25, 2007
I have a macro that deletes rows. For some reason, the macro bombs out when the selection seems to be too big. Why is that?
deleterow_min = Sheets("Sheet1").Cells(1, 6)
deleterow_max = Sheets("Sheet1").Cells(1, 7)
Rows(deleterow_min & ":" & deleterow_max).Select
Selection.Delete Shift:=xlUp
View 9 Replies
View Related
Aug 5, 2009
In a part of my code I have something that will delete all hidden rows, like:
For Z = 1 To TotalRows - 1
If Cells(TotalRows - Z, 1).EntireRow.Hidden Then
Cells(TotalRows - Z, 1).EntireRow.Delete
End If
Next Z
I've done it in reverse to be quicker, but it's still quite slow. Granted, I am deleting several thousand rows, but surely there's a quicker way? Thinking about it logically I would presume that (in general) selecting stuff first in Excel and then deleting them appears to be quicker, but I'm not sure how to pull that off.
View 9 Replies
View Related
Feb 21, 2009
Here's my problem. I have the following table:
View 5 Replies
View Related
