I am using a macro that creates a copy of a specific tab, then on the new tab it deletes rows that have nothing in a specific range of cells.
When I run the macro today, I first get the excel error Excel cannot complete this task with available resources... when I select OK I get the macro error Run-time error '1004': Delete method of range class failed. I have been using this macro for a while with no problems and have not made any changes to the macro.
here is the macro:
Sub AddSheet3()
LastRow = Range("A1048576").End(xlUp).Row
Dim SheetName
Dim rng As Range
Dim i As Integer, counter As Integer
SheetName = Sheets("Journal Import").Range("C1")
Sheets("Journal Import").Select
Columns("C:C").Select
Selection.Replace What:="- total", Replacement:="-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Journal Import").Copy After:=Sheets(6)
ActiveSheet.Buttons.Delete
Range("a2:h" & LastRow).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set rng = Range("h2:h" & LastRow)
i = 1
For counter = 1 To rng.Rows.Count
If rng.Cells(i) = "" Then
rng.Cells(i).EntireRow.Delete
Else
i = i + 1
End If
Next
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("x1").Select
Selection.Copy
Range("d1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("I:xfd").Select
Selection.Delete Shift:=xlToLeft
LastRow = Range("A1048576").End(xlUp).Row
Rows(LastRow + 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Name = SheetName
ActiveSheet.Select
ActiveSheet.Move
End Sub
The macro works fine at the beginning but when it gets to "Set rng = Range("h2:h" & LastRow)" and starts looping thru the rng it errors out when it gets to the first row with "". When I debug the line that it stops on is "rng.Cells(i).EntireRow.Delete".
What is the problem? Any and all help is great.
Bookmarks