Excel Crashes after Delete Visible Rows (Filtered selection) VBA

tonyjyoo

Board Regular
Joined
Aug 5, 2016
Messages
167
Hello,

I have a macro that basically pulls in data for current month, spits it into my workbook, then extracts data from the previous month, pastes into my workbook. Within my workbook, I have a formula to detect which ones are "duplicates", and my VBA filters my data by "Duplicates" and deletes visible rows.

This macro works fine for 3 other worksheets I have, but I keep having problems with one.

Below is my macro:

Code:
Sub UpdateEmployeeAllocationList()


'Automatically update Employee Allocation List with 2 latest files to account for terminations.


Dim Main As Workbook
Dim Month As Workbook


'Optimization
    Application.ScreenUpdating = False


R = "B4:L2000"
R1 = "A3:K1000"


Range(R).Clear


Set Main = ThisWorkbook
file = Range("A1").Value
Set Month = Workbooks.Open("S:\NPH Accounting\NPH Accounting Department\Allocations\2017\Employee Allocation\" & file & " Employee Allocations 2017.xlsx")


'the below is the workbook where the data will be copied from it is getting activated
Month.Activate
Month.Sheets("Employee Allocation List").Select
'now when the above workbook is activated the data from its active sheet will be selected and copied
Month.Sheets("Employee Allocation List").Range(R1).Select
Application.CutCopyMode = False
Selection.Copy
'now the sheet where you are running this macro from is being activated
Main.Activate
'Now you will select and change focus to the sheet where you want to copy your data
Main.Sheets("Employee Allocation List").Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


Application.CutCopyMode = False


'Determine which month data is from.


    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With




Month.Close False


    Range("A4:L4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 6299648
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    
Columns("H:L").Select
    Selection.NumberFormat = "0.00%"




'Now for 2nd Employee Allocation List




R = "B4:L2000"
R1 = "A4:K1000"






Set Main = ThisWorkbook
file2 = Range("A2").Value
Set Month2 = Workbooks.Open("S:\NPH Accounting\NPH Accounting Department\Allocations\2017\Employee Allocation\" & file2 & " Employee Allocations 2017.xlsx")


'the below is the workbook where the data will be copied from it is getting activated
Month2.Activate
Month2.Sheets("Employee Allocation List").Select
'now when the above workbook is activated the data from its active sheet will be selected and copied
Month2.Sheets("Employee Allocation List").Range(R1).Select
Application.CutCopyMode = False
Selection.Copy
'now the sheet where you are running this macro from is being activated
Main.Activate
'Now you will select and change focus to the sheet where you want to copy your data
Main.Sheets("Employee Allocation List").Activate


Range("B1999").Select
Selection.End(xlUp).Select
    ActiveCell.Offset(1).Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


ActiveCell.Offset(1).Select
ActiveCell.Offset(-1).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    
    
Application.CutCopyMode = False




    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With


Month2.Close False


'Delete duplicates from employee allocation list


    ActiveSheet.Range("$A$4:$O$2000").AutoFilter Field:=15, Criteria1:= _
        "Duplicate"
    Range("A5:O2000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.Range("$A$4:$O$2000").AutoFilter Field:=15



'Add Formulated rows for next update


    Range("A5").Select
    Selection.Copy
    Range("A5:A2000").Select
    ActiveSheet.Paste
    
    Range("N5:O5").Select
    Selection.Copy
    Range("N5:O2000").Select
    ActiveSheet.Paste




Application.ScreenUpdating = True


 'To the Top!
    Application.Goto Reference:=Range("a1"), Scroll:=True


End Sub
It works fine up until this point:
Code:
    ActiveSheet.Range("$A$4:$O$2000").AutoFilter Field:=15, Criteria1:= _
        "Duplicate"
    Range("A5:O2000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.Range("$A$4:$O$2000").AutoFilter Field:=15
in which it gives me the following error:
Code:
Run-time error '-2147417848 (80010108)':

Method 'Delete' of object 'Range' failed
I don't understand why only this workbook has issues. My other ones have identical tabs and macro.

Is it memory? Is the file corrupt? Any advice GREATLY appreciated.

Tony
 

tonyjyoo

Board Regular
Joined
Aug 5, 2016
Messages
167
Hello,

I have a macro that basically pulls in data for current month, spits it into my workbook, then extracts data from the previous month, pastes into my workbook. Within my workbook, I have a formula to detect which ones are "duplicates", and my VBA filters my data by "Duplicates" and deletes visible rows.

This macro works fine for 3 other worksheets I have, but I keep having problems with one.

Below is my macro:

Code:
Sub UpdateEmployeeAllocationList()


'Automatically update Employee Allocation List with 2 latest files to account for terminations.


Dim Main As Workbook
Dim Month As Workbook


'Optimization
    Application.ScreenUpdating = False


R = "B4:L2000"
R1 = "A3:K1000"


Range(R).Clear


Set Main = ThisWorkbook
file = Range("A1").Value
Set Month = Workbooks.Open("S:\NPH Accounting\NPH Accounting Department\Allocations\2017\Employee Allocation\" & file & " Employee Allocations 2017.xlsx")


'the below is the workbook where the data will be copied from it is getting activated
Month.Activate
Month.Sheets("Employee Allocation List").Select
'now when the above workbook is activated the data from its active sheet will be selected and copied
Month.Sheets("Employee Allocation List").Range(R1).Select
Application.CutCopyMode = False
Selection.Copy
'now the sheet where you are running this macro from is being activated
Main.Activate
'Now you will select and change focus to the sheet where you want to copy your data
Main.Sheets("Employee Allocation List").Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


Application.CutCopyMode = False


'Determine which month data is from.


    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With




Month.Close False


    Range("A4:L4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 6299648
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    
Columns("H:L").Select
    Selection.NumberFormat = "0.00%"




'Now for 2nd Employee Allocation List




R = "B4:L2000"
R1 = "A4:K1000"






Set Main = ThisWorkbook
file2 = Range("A2").Value
Set Month2 = Workbooks.Open("S:\NPH Accounting\NPH Accounting Department\Allocations\2017\Employee Allocation\" & file2 & " Employee Allocations 2017.xlsx")


'the below is the workbook where the data will be copied from it is getting activated
Month2.Activate
Month2.Sheets("Employee Allocation List").Select
'now when the above workbook is activated the data from its active sheet will be selected and copied
Month2.Sheets("Employee Allocation List").Range(R1).Select
Application.CutCopyMode = False
Selection.Copy
'now the sheet where you are running this macro from is being activated
Main.Activate
'Now you will select and change focus to the sheet where you want to copy your data
Main.Sheets("Employee Allocation List").Activate


Range("B1999").Select
Selection.End(xlUp).Select
    ActiveCell.Offset(1).Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


ActiveCell.Offset(1).Select
ActiveCell.Offset(-1).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    
    
Application.CutCopyMode = False




    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With


Month2.Close False


'Delete duplicates from employee allocation list


    ActiveSheet.Range("$A$4:$O$2000").AutoFilter Field:=15, Criteria1:= _
        "Duplicate"
    Range("A5:O2000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.Range("$A$4:$O$2000").AutoFilter Field:=15



'Add Formulated rows for next update


    Range("A5").Select
    Selection.Copy
    Range("A5:A2000").Select
    ActiveSheet.Paste
    
    Range("N5:O5").Select
    Selection.Copy
    Range("N5:O2000").Select
    ActiveSheet.Paste




Application.ScreenUpdating = True


 'To the Top!
    Application.Goto Reference:=Range("a1"), Scroll:=True


End Sub
It works fine up until this point:
Code:
    ActiveSheet.Range("$A$4:$O$2000").AutoFilter Field:=15, Criteria1:= _
        "Duplicate"
    Range("A5:O2000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.Range("$A$4:$O$2000").AutoFilter Field:=15
in which it gives me the following error:
Code:
Run-time error '-2147417848 (80010108)':

Method 'Delete' of object 'Range' failed
I don't understand why only this workbook has issues. My other ones have identical tabs and macro.

Is it memory? Is the file corrupt? Any advice GREATLY appreciated.

Tony
Bump.
 

Forum statistics

Threads
1,081,860
Messages
5,361,737
Members
400,653
Latest member
ProParadox

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top