Combined code not working

[Joel Mills]

The following code (below) has been pieced together from individual modules
and works when run from the worksheet titled "Charts!" When I try to run it
from another sheet I get the following message : Runtime error '1004'
Selection method of range class failed and the debug highlights the
following line of code:

Range("Charts!b25:b56").Select




Sub Delete_CR()
Dim Rng As Range, rng1 As Range
On Error Resume Next
Set Rng = Range("Charts!B25:IV26").SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0
If Not Rng Is Nothing Then Rng.EntireColumn.Delete
Dim n As Long, lastrow As Long
lastrow = Range("Charts!B52").End(xlUp).Row
For n = lastrow To 2 Step -1
If Cells(n, 2) = "Grand Total" Or Cells(n, 2) = "0" _
Then Cells(n, 2).EntireRow.Delete
Next n
Range("Charts!b25:b56").Select
For Each Rng In Selection.Cells
If Rng.Interior.ColorIndex = 1 Then
Rng.EntireRow.Hidden = True
End If
Next Rng
End Sub

 

[Guest]

hi,
that line of code is trying to select a range on a sheet
named charts and it can't find charts.
go through the code and change "charts" to the sheet in
the new workbook.

 

[Joel Mills]

Thanks for the reply. I don't think I explained my problem very well. The
code works okay from the "Charts" worksheet, but not from another worksheet
within the same workbook. I am not trying to run it from a new workbook.
When I run the macro from the work sheet titled "Chart" it works, but it
doesn't when I run it from another worksheet it the same workbook.

 

[Jim Cone]

Joel,

To select a range, the sheet must be active.
Also, you generally do Not have to select something in order to
manipulate it with VBA code.

See if the following modified code does what you want...
'---------------------------------
Sub Delete_CR()
Dim Rng As Excel.Range
Dim rng1 As Excel.Range
Dim N As Long
Dim LastRow As Long

On Error Resume Next
Set Rng = Worksheets("Charts").Range("B25:IV26").SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0

If Not Rng Is Nothing Then Rng.EntireColumn.Delete
LastRow = Worksheets("Charts").Range("B52").End(xlUp).Row

For N = LastRow To 2 Step -1
With Worksheets("Charts").Cells(N, 2)
If .Value = "Grand Total" Or .Value = "0" Then
.EntireRow.Delete
End If
End With
Next N

Set Rng = Worksheets("Charts").Range("B25:B56")
For Each rng1 In Rng
If rng1.Interior.ColorIndex = 1 Then
rng1.EntireRow.Hidden = True
End If
Next ' rng1

Set Rng = Nothing
Set rng1 = Nothing
End Sub
'---------------------------------

Regards,
Jim Cone
San Francisco, CA

 

[Joel Mills]

Jim,

Your revisions worked great. I am very new to using VBA and simple tried
copying several code strings into one module to simplify running a macro.
Once again thanks for your help.

 

[Jim Cone]

Joel,
You are welcome.
Getting feedback on the solution offered is doubly appreciated.
Regards,
Jim Cone
San Francisco, CA

Jim,
Your revisions worked great. I am very new to using VBA and simple tried
copying several code strings into one module to simplify running a macro.
Once again thanks for your help.

-snip-