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

Clear Rows Vs. Deleting Rows To Avoid #REF! Formula Error - Macro

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

Prevent #REF! Error When Deleting Rows

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

Deleting Rows Is Causing Error Message

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

Getting #VALUE Error When Deleting Blank Rows With VBA Code

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

Deleting Empty Rows - Range Class Failed Error

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

Prevent Linked Cells Showing Error When Deleting Rows On Other Sheet

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

Deleting Duplicate Rows Based On Earliest Or Latest Time?

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

Insert Static Timestamp But Receive Runtime Error When Deleting Rows In Sheet

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

Deleting Rows Macro :: Keep Full Time Sales People Delete Else

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

Run Time Error 1004 When Adding Rows

Sep 27, 2009

Run time error 1004 when adding rows. I have the following code, called from a Userform:

View 4 Replies View Related

Run-Time Error '1004' :: Hiding Blank Rows

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

Run Time Error When Copying Rows Based On Word

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

Excel 2010 :: Delete Filtered Rows Without Deleting The Hidden Rows?

Sep 25, 2012

How do I delete filtered rows without deleting the hidden rows in excel 2010?

View 8 Replies View Related

Error Message When Deleting Worksheet

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

Code For Deleting Rows Verses Hiding Rows

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

Delete Blank Rows (formula Not Deleting All Rows)

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

Error Clearing Filter After Deleting Records

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

Code Deleting Non-Existent Module Causes Error

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

Run-time Error '91' When 'On Error Goto' And Cells.find

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

Error Deleting Range Name In Kanji (Japanese) Language

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

Deleting Rows Macro :: Rows That Don't Contain Word ...

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

Deleting Sheets Based On Name - Runtime Error Object Required

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

Run-time Error Every Other Time The Macro Runs

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

Deleting Rows/content Of Rows

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

VBA And Deleting Rows

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

Deleting Rows That Contain Particular Value

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

Deleting Rows

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

Deleting The All Rows

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

Deleting Specific Rows..

Feb 21, 2009

Here's my problem. I have the following table:

View 5 Replies View Related







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