Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combined code not working
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combined code not working
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. -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combined code not working
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. wrote in message ... 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. -----Original Message----- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combined code not working
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").SpecialCell s(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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combined code not working
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" wrote in message ... 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").SpecialCell s(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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combined code not working
Joel,
You are welcome. Getting feedback on the solution offered is doubly appreciated. Regards, Jim Cone San Francisco, CA "Joel Mills" wrote in message ... 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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB code is not working like it should | Excel Programming | |||
Code not working | Excel Programming | |||
Code not Working - Help please | Excel Programming | |||
Code not working | Excel Programming | |||
For Each Code Not Working | Excel Programming |