Halp
04-16-2014, 06:47 AM
When writing this program I blocked everything out into different subs. It seems like the "Desired To be Removed Text Here" at the bottom is what is causing the crashes. When the code is ran the system will sit for 3-5 min working perfectly and then excel will crash. If you attempt to save the spreadsheet at any time after you have run the code excel will crash. Can anyone please assist? I really need some fresh eyes on this I've exhausted my skills to troubleshoot this problem and I am not to up to date on excel. Thank you!
Sub Whatever()
'After you have added in all the CSV files run macro
'This formats column A the format "Number" and column G and H to the format "11-Jan-11"
Columns(1).NumberFormat = "0"
ActiveSheet.Range("G17", "G2000").NumberFormat = "dd-MMM-yy:"
ActiveSheet.Range("H17", "H2000").NumberFormat = "dd-MMM-yy;"
'This sorts by the status
' "o" Variable for Priority sort
' "s" Variable for Secondary sort
Dim oWorksheet As Worksheet
Set oWorksheet = ActiveWorkbook.Worksheets("Details for Period")
Dim oRangeSort As Range
Dim sRangeSort As Range
Dim oRangeKey As Range
Dim sRangeKey As Range
' one range that includes all colums do sort "A17, K2000"
Set oRangeSort = oWorksheet.Range("A17:K2000")
Set sRangeSort = oWorksheet.Range("A17:K2000")
' Set Columns It will be Sorting by
Set oRangeKey = oWorksheet.Range("I1")
Set sRangeKey = oWorksheet.Range("F1")
' Custom Sort List
Dim sCustomList(1 To 5) As String
sCustomList(1) = "Assigned"
sCustomList(2) = "Work In Progress"
sCustomList(3) = "Pending"
sCustomList(4) = "Resolved"
sCustomList(5) = "Closed"
Application.AddCustomList ListArray:=sCustomList
oWorksheet.Sort.SortFields.Clear
oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Key2:=sRangeKey, Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
' clean up
Application.DeleteCustomList Application.CustomListCount
Set oWorksheet = Nothing
' Clears all Instances of "Desired To be Removed Text Here" from Column E
Dim i
For i = 1500 To 1 Step -1
If Cells(i, "E").Value = "Desired To be Removed Text Here" Then
Cells(i, "E").EntireRow.Delete
End If
Next
End Sub
Sub Whatever()
'After you have added in all the CSV files run macro
'This formats column A the format "Number" and column G and H to the format "11-Jan-11"
Columns(1).NumberFormat = "0"
ActiveSheet.Range("G17", "G2000").NumberFormat = "dd-MMM-yy:"
ActiveSheet.Range("H17", "H2000").NumberFormat = "dd-MMM-yy;"
'This sorts by the status
' "o" Variable for Priority sort
' "s" Variable for Secondary sort
Dim oWorksheet As Worksheet
Set oWorksheet = ActiveWorkbook.Worksheets("Details for Period")
Dim oRangeSort As Range
Dim sRangeSort As Range
Dim oRangeKey As Range
Dim sRangeKey As Range
' one range that includes all colums do sort "A17, K2000"
Set oRangeSort = oWorksheet.Range("A17:K2000")
Set sRangeSort = oWorksheet.Range("A17:K2000")
' Set Columns It will be Sorting by
Set oRangeKey = oWorksheet.Range("I1")
Set sRangeKey = oWorksheet.Range("F1")
' Custom Sort List
Dim sCustomList(1 To 5) As String
sCustomList(1) = "Assigned"
sCustomList(2) = "Work In Progress"
sCustomList(3) = "Pending"
sCustomList(4) = "Resolved"
sCustomList(5) = "Closed"
Application.AddCustomList ListArray:=sCustomList
oWorksheet.Sort.SortFields.Clear
oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Key2:=sRangeKey, Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
' clean up
Application.DeleteCustomList Application.CustomListCount
Set oWorksheet = Nothing
' Clears all Instances of "Desired To be Removed Text Here" from Column E
Dim i
For i = 1500 To 1 Step -1
If Cells(i, "E").Value = "Desired To be Removed Text Here" Then
Cells(i, "E").EntireRow.Delete
End If
Next
End Sub