How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask The-PC-Guy Your Own Question
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1997
Experience:  20 years experience providing remote computer support
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

Can you help me understand why I am getting a 1004 error

Customer Question

Can you help me understand why I am getting a 1004 error in a Macro in Excel
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Pete replied 1 year ago.
Hi Charles,The 1004 error occurs when the Excel chart contains more legend entries than there is space available to display the legend entries on the Excel chart. When this behavior occurs, Microsoft Excel may truncate the legend entries.Because the LegendEntries method in your macro uses what appears for the legend (in this case, the truncated legend entries), the error message that is mentioned in the "Symptoms" section of this article occurs when there are more entries than there is space available to display the legend entries on the Excel chart.
Customer: replied 1 year ago.
Here is the Macro in question: The goal is to eliminate all of the space excel is using because of dead space.
In the example the value of last column is 11 and row is 2244. However the last column goes on forever in the actual workbook. The 1004 error is pointing to this statement:
.Range(.Cells(1, LastCol + 1), .Cells(.Rows.Count, .Columns.Count)).Delete
I don't know why.Sub ExcelDiet()Dim j As Long
Dim k As Long
Dim LastRow As Long
Dim LastCol As Long
Dim ColFormula As Range
Dim RowFormula As Range
Dim ColValue As Range
Dim RowValue As Range
Dim Shp As Shape
Dim ws As WorksheetApplication.ScreenUpdating = False
Application.DisplayAlerts = FalseOn Error Resume NextFor Each ws In Worksheets
With wsIf ws.Name <> "Run Data" Then'Find the last used cell with a formula and value
'Search by Columns and Rows
On Error Resume Next
Set ColFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
Set ColValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
Set RowFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Set RowValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
On Error GoTo 0'Determine the last column
If ColFormula Is Nothing Then
LastCol = 0
LastCol = ColFormula.Column
End If
If Not ColValue Is Nothing Then
LastCol = Application.WorksheetFunction.Max(LastCol, ColValue.Column)
End If'Determine the last row
If RowFormula Is Nothing Then
LastRow = 0
LastRow = RowFormula.Row
End If
If Not RowValue Is Nothing Then
LastRow = Application.WorksheetFunction.Max(LastRow, RowValue.Row)
End If'Determine if any shapes are beyond the last row and last column
For Each Shp In .Shapes
j = 0
k = 0
On Error Resume Next
j = Shp.TopLeftCell.Row
k = Shp.TopLeftCell.Column
On Error GoTo 0
If j > 0 And k > 0 Then
Do Until .Cells(j, k).Top > Shp.Top + Shp.Height
j = j + 1
If j > LastRow Then
LastRow = j
End If
Do Until .Cells(j, k).Left > Shp.Left + Shp.Width
k = k + 1
If k > LastCol Then
LastCol = k
End If
End If
.Range(.Cells(1, LastCol + 1), .Cells(.Rows.Count, .Columns.Count)).Delete
.Range(.Cells(LastRow + 1, 1), .Cells(.Rows.Count, .Columns.Count)).Delete
End If
End With
NextApplication.ScreenUpdating = True
Application.DisplayAlerts = TrueEnd Sub
Expert:  Pete replied 1 year ago.
I cannot find the exact cause of the error - let me see if another expert can assist with this...
Customer: replied 1 year ago.
Thank you
Expert:  The-PC-Guy replied 1 year ago.
what line are you getting the error on