Hello-

I seem to have done a poor job explaining what I am trying to do. In order
to be more clear I am submitting most of my entire macro.

First I loop
through the cell range a:17 to av:56 and copy all rows with the criteria in
column AR = “True” *(a label) to another worksheet.. This loop works very
well.

Problem:
Then I want to delete certain cells in the rows (the same rows that the
first loop copied)–-only this time I want to delete certain cells in that
row---not the
entire row because many of the rows contain formulas.

I noted below where the problem starts. Currently I have some code there,
but it doesn’t do the job.I have tried both loops and auto filters. Cannot
get either to work.


I feel like this should be simple, but I’ve never been so stumped.


Sub MoveCompletedTradesLoop()

Application.Run "Unprotect"
'Define Variables
Dim TradesEntered As Range, ClosCheck As Range

With Sheets("Analysis")
Set TradesEntered = .Range("at17:at56")
End With


'Loop: Check for complete trades, copy to Trade History
For X = 1 To TradesEntered.Count
Set ClosCheck = TradesEntered(X)

' For Each PastCheck In TradesEnteredPast


If ClosCheck.Value = "True" Then
With ClosCheck
..Worksheet.Select
ClosCheck.EntireRow.Select
Selection.Copy
Sheets("TradeHistory").Select
Range("A4").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
ActiveCell.EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Sheets("Analysis").Select
Range("A1").Select
End With
'Else
' MsgBox ("OK") 'Goes with Else. Comment out
' Exit Sub 'Goes with Else. Comment it out.
End If
Next 'Ends "For Each" Loop
'Unprotect, Unhide TradeHistory & Analysis Sheets

THIS IS WHERE I HAVE PROBLEMS—DO FAR EVERYTHING I TRIED EITHER DOESN’T WORK
OR ERRORS OUT.
'Dim TradesEntered As Range, ClosCheck As Range
'Sheets("Analysis").Select

With ActiveSheet
'remove the existing filter
..AutoFilterMode = False
..Range("ar17:ar56").AutoFilter field:=46, Criteria1:="True"
'in case there are no visible cells
On Error Resume Next
..Range("A17:F56").SpecialCells(xlCellTypeVisible).ClearContents
..Range("K17:M56").SpecialCells(xlCellTypeVisible).ClearContents
..Range("O17:S56").SpecialCells(xlCellTypeVisible).ClearContents
On Error GoTo 0
..AutoFilterMode = False
End With

Hi Andy

If I understand you correct you only want to delete cells in the row with no formula.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Andyjim" <(E-Mail Removed)> wrote in message news:B9897768-E30E-4572-B626-(E-Mail Removed)...
> Hello-
>
> I seem to have done a poor job explaining what I am trying to do. In order
> to be more clear I am submitting most of my entire macro.
>
> First I loop
> through the cell range a:17 to av:56 and copy all rows with the criteria in
> column AR = “True” *(a label) to another worksheet.. This loop works very
> well.
>
> Problem:
> Then I want to delete certain cells in the rows (the same rows that the
> first loop copied)–-only this time I want to delete certain cells in that
> row---not the
> entire row because many of the rows contain formulas.
>
> I noted below where the problem starts. Currently I have some code there,
> but it doesn’t do the job.I have tried both loops and auto filters. Cannot
> get either to work.
>
>
> I feel like this should be simple, but I’ve never been so stumped.
>
>
> Sub MoveCompletedTradesLoop()
>
> Application.Run "Unprotect"
> 'Define Variables
> Dim TradesEntered As Range, ClosCheck As Range
>
> With Sheets("Analysis")
> Set TradesEntered = .Range("at17:at56")
> End With
>
>
> 'Loop: Check for complete trades, copy to Trade History
> For X = 1 To TradesEntered.Count
> Set ClosCheck = TradesEntered(X)
>
> ' For Each PastCheck In TradesEnteredPast
>
>
> If ClosCheck.Value = "True" Then
> With ClosCheck
> .Worksheet.Select
> ClosCheck.EntireRow.Select
> Selection.Copy
> Sheets("TradeHistory").Select
> Range("A4").Activate
> Selection.End(xlDown).Select
> ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
> ActiveCell.EntireRow.Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> Range("A1").Select
> Sheets("Analysis").Select
> Range("A1").Select
> End With
> 'Else
> ' MsgBox ("OK") 'Goes with Else. Comment out
> ' Exit Sub 'Goes with Else. Comment it out.
> End If
> Next 'Ends "For Each" Loop
> 'Unprotect, Unhide TradeHistory & Analysis Sheets
>
> THIS IS WHERE I HAVE PROBLEMS—DO FAR EVERYTHING I TRIED EITHER DOESN’T WORK
> OR ERRORS OUT.
> 'Dim TradesEntered As Range, ClosCheck As Range
> 'Sheets("Analysis").Select
>
> With ActiveSheet
> 'remove the existing filter
> .AutoFilterMode = False
> .Range("ar17:ar56").AutoFilter field:=46, Criteria1:="True"
> 'in case there are no visible cells
> On Error Resume Next
> .Range("A17:F56").SpecialCells(xlCellTypeVisible).ClearContents
> .Range("K17:M56").SpecialCells(xlCellTypeVisible).ClearContents
> .Range("O17:S56").SpecialCells(xlCellTypeVisible).ClearContents
> On Error GoTo 0
> .AutoFilterMode = False
> End With
>

Hi Ron-
Yes. Every row has some columns with formulas. I need to delete only the
cells in that row that do not have formulas. THey include a17:f56, k17:m56,
and o17:s56 - everywhere column AT = the label "True". Thanks

"Ron de Bruin" wrote:

> Hi Andy
>
> If I understand you correct you only want to delete cells in the row with no formula.
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Andyjim" <(E-Mail Removed)> wrote in message news:B9897768-E30E-4572-B626-(E-Mail Removed)...
> > Hello-
> >
> > I seem to have done a poor job explaining what I am trying to do. In order
> > to be more clear I am submitting most of my entire macro.
> >
> > First I loop
> > through the cell range a:17 to av:56 and copy all rows with the criteria in
> > column AR = “True” *(a label) to another worksheet.. This loop works very
> > well.
> >
> > Problem:
> > Then I want to delete certain cells in the rows (the same rows that the
> > first loop copied)–-only this time I want to delete certain cells in that
> > row---not the
> > entire row because many of the rows contain formulas.
> >
> > I noted below where the problem starts. Currently I have some code there,
> > but it doesn’t do the job.I have tried both loops and auto filters. Cannot
> > get either to work.
> >
> >
> > I feel like this should be simple, but I’ve never been so stumped.
> >
> >
> > Sub MoveCompletedTradesLoop()
> >
> > Application.Run "Unprotect"
> > 'Define Variables
> > Dim TradesEntered As Range, ClosCheck As Range
> >
> > With Sheets("Analysis")
> > Set TradesEntered = .Range("at17:at56")
> > End With
> >
> >
> > 'Loop: Check for complete trades, copy to Trade History
> > For X = 1 To TradesEntered.Count
> > Set ClosCheck = TradesEntered(X)
> >
> > ' For Each PastCheck In TradesEnteredPast
> >
> >
> > If ClosCheck.Value = "True" Then
> > With ClosCheck
> > .Worksheet.Select
> > ClosCheck.EntireRow.Select
> > Selection.Copy
> > Sheets("TradeHistory").Select
> > Range("A4").Activate
> > Selection.End(xlDown).Select
> > ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
> > ActiveCell.EntireRow.Select
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Application.CutCopyMode = False
> > Range("A1").Select
> > Sheets("Analysis").Select
> > Range("A1").Select
> > End With
> > 'Else
> > ' MsgBox ("OK") 'Goes with Else. Comment out
> > ' Exit Sub 'Goes with Else. Comment it out.
> > End If
> > Next 'Ends "For Each" Loop
> > 'Unprotect, Unhide TradeHistory & Analysis Sheets
> >
> > THIS IS WHERE I HAVE PROBLEMS—DO FAR EVERYTHING I TRIED EITHER DOESN’T WORK
> > OR ERRORS OUT.
> > 'Dim TradesEntered As Range, ClosCheck As Range
> > 'Sheets("Analysis").Select
> >
> > With ActiveSheet
> > 'remove the existing filter
> > .AutoFilterMode = False
> > .Range("ar17:ar56").AutoFilter field:=46, Criteria1:="True"
> > 'in case there are no visible cells
> > On Error Resume Next
> > .Range("A17:F56").SpecialCells(xlCellTypeVisible).ClearContents
> > .Range("K17:M56").SpecialCells(xlCellTypeVisible).ClearContents
> > .Range("O17:S56").SpecialCells(xlCellTypeVisible).ClearContents
> > On Error GoTo 0
> > .AutoFilterMode = False
> > End With
> >
>
>

Ok, test this on for the activesheet

If column AT = "True" it will delete all constants in that row

Sub Loop_Example()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With ActiveSheet

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

'We check the values in the AT column in this example
With .Cells(Lrow, "AT")

If Not IsError(.Value) Then

If .Value = "True" Then
On Error Resume Next
.Rows(Lrow).SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End If

End If

End With

Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Andyjim" <(E-Mail Removed)> wrote in message news:EEF14F25-936D-4DC0-8CA8-(E-Mail Removed)...
> Hi Ron-
> Yes. Every row has some columns with formulas. I need to delete only the
> cells in that row that do not have formulas. THey include a17:f56, k17:m56,
> and o17:s56 - everywhere column AT = the label "True". Thanks
>
> "Ron de Bruin" wrote:
>
>> Hi Andy
>>
>> If I understand you correct you only want to delete cells in the row with no formula.
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Andyjim" <(E-Mail Removed)> wrote in message news:B9897768-E30E-4572-B626-(E-Mail Removed)...
>> > Hello-
>> >
>> > I seem to have done a poor job explaining what I am trying to do. In order
>> > to be more clear I am submitting most of my entire macro.
>> >
>> > First I loop
>> > through the cell range a:17 to av:56 and copy all rows with the criteria in
>> > column AR = “True” *(a label) to another worksheet.. This loop works very
>> > well.
>> >
>> > Problem:
>> > Then I want to delete certain cells in the rows (the same rows that the
>> > first loop copied)–-only this time I want to delete certain cells in that
>> > row---not the
>> > entire row because many of the rows contain formulas.
>> >
>> > I noted below where the problem starts. Currently I have some code there,
>> > but it doesn’t do the job.I have tried both loops and auto filters. Cannot
>> > get either to work.
>> >
>> >
>> > I feel like this should be simple, but I’ve never been so stumped.
>> >
>> >
>> > Sub MoveCompletedTradesLoop()
>> >
>> > Application.Run "Unprotect"
>> > 'Define Variables
>> > Dim TradesEntered As Range, ClosCheck As Range
>> >
>> > With Sheets("Analysis")
>> > Set TradesEntered = .Range("at17:at56")
>> > End With
>> >
>> >
>> > 'Loop: Check for complete trades, copy to Trade History
>> > For X = 1 To TradesEntered.Count
>> > Set ClosCheck = TradesEntered(X)
>> >
>> > ' For Each PastCheck In TradesEnteredPast
>> >
>> >
>> > If ClosCheck.Value = "True" Then
>> > With ClosCheck
>> > .Worksheet.Select
>> > ClosCheck.EntireRow.Select
>> > Selection.Copy
>> > Sheets("TradeHistory").Select
>> > Range("A4").Activate
>> > Selection.End(xlDown).Select
>> > ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
>> > ActiveCell.EntireRow.Select
>> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>> > SkipBlanks _
>> > :=False, Transpose:=False
>> > Application.CutCopyMode = False
>> > Range("A1").Select
>> > Sheets("Analysis").Select
>> > Range("A1").Select
>> > End With
>> > 'Else
>> > ' MsgBox ("OK") 'Goes with Else. Comment out
>> > ' Exit Sub 'Goes with Else. Comment it out.
>> > End If
>> > Next 'Ends "For Each" Loop
>> > 'Unprotect, Unhide TradeHistory & Analysis Sheets
>> >
>> > THIS IS WHERE I HAVE PROBLEMS—DO FAR EVERYTHING I TRIED EITHER DOESN’T WORK
>> > OR ERRORS OUT.
>> > 'Dim TradesEntered As Range, ClosCheck As Range
>> > 'Sheets("Analysis").Select
>> >
>> > With ActiveSheet
>> > 'remove the existing filter
>> > .AutoFilterMode = False
>> > .Range("ar17:ar56").AutoFilter field:=46, Criteria1:="True"
>> > 'in case there are no visible cells
>> > On Error Resume Next
>> > .Range("A17:F56").SpecialCells(xlCellTypeVisible).ClearContents
>> > .Range("K17:M56").SpecialCells(xlCellTypeVisible).ClearContents
>> > .Range("O17:S56").SpecialCells(xlCellTypeVisible).ClearContents
>> > On Error GoTo 0
>> > .AutoFilterMode = False
>> > End With
>> >
>>
>>

Oops

Typo, use this

.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

Instead of
.Rows(Lrow).SpecialCells(xlCellTypeConstants).ClearContents



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Ok, test this on for the activesheet
>
> If column AT = "True" it will delete all constants in that row
>
> Sub Loop_Example()
> Dim Firstrow As Long
> Dim Lastrow As Long
> Dim Lrow As Long
> Dim CalcMode As Long
> Dim ViewMode As Long
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> 'We use the ActiveSheet but you can replace this with
> 'Sheets("MySheet")if you want
> With ActiveSheet
>
> 'We select the sheet so we can change the window view
> .Select
>
> 'If you are in Page Break Preview Or Page Layout view go
> 'back to normal view, we do this for speed
> ViewMode = ActiveWindow.View
> ActiveWindow.View = xlNormalView
>
> 'Turn off Page Breaks, we do this for speed
> .DisplayPageBreaks = False
>
> 'Set the first and last row to loop through
> Firstrow = .UsedRange.Cells(1).Row
> Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
>
> 'We loop from Lastrow to Firstrow (bottom to top)
> For Lrow = Lastrow To Firstrow Step -1
>
> 'We check the values in the AT column in this example
> With .Cells(Lrow, "AT")
>
> If Not IsError(.Value) Then
>
> If .Value = "True" Then
> On Error Resume Next
> .Rows(Lrow).SpecialCells(xlCellTypeConstants).ClearContents
> On Error GoTo 0
> End If
>
> End If
>
> End With
>
> Next Lrow
>
> End With
>
> ActiveWindow.View = ViewMode
> With Application
> .ScreenUpdating = True
> .Calculation = CalcMode
> End With
>
> End Sub
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Andyjim" <(E-Mail Removed)> wrote in message news:EEF14F25-936D-4DC0-8CA8-(E-Mail Removed)...
>> Hi Ron-
>> Yes. Every row has some columns with formulas. I need to delete only the
>> cells in that row that do not have formulas. THey include a17:f56, k17:m56,
>> and o17:s56 - everywhere column AT = the label "True". Thanks
>>
>> "Ron de Bruin" wrote:
>>
>>> Hi Andy
>>>
>>> If I understand you correct you only want to delete cells in the row with no formula.
>>>
>>>
>>> --
>>>
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl/tips.htm
>>>
>>>
>>> "Andyjim" <(E-Mail Removed)> wrote in message news:B9897768-E30E-4572-B626-(E-Mail Removed)...
>>> > Hello-
>>> >
>>> > I seem to have done a poor job explaining what I am trying to do. In order
>>> > to be more clear I am submitting most of my entire macro.
>>> >
>>> > First I loop
>>> > through the cell range a:17 to av:56 and copy all rows with the criteria in
>>> > column AR = “True” *(a label) to another worksheet.. This loop works very
>>> > well.
>>> >
>>> > Problem:
>>> > Then I want to delete certain cells in the rows (the same rows that the
>>> > first loop copied)–-only this time I want to delete certain cells in that
>>> > row---not the
>>> > entire row because many of the rows contain formulas.
>>> >
>>> > I noted below where the problem starts. Currently I have some code there,
>>> > but it doesn’t do the job.I have tried both loops and auto filters. Cannot
>>> > get either to work.
>>> >
>>> >
>>> > I feel like this should be simple, but I’ve never been so stumped.
>>> >
>>> >
>>> > Sub MoveCompletedTradesLoop()
>>> >
>>> > Application.Run "Unprotect"
>>> > 'Define Variables
>>> > Dim TradesEntered As Range, ClosCheck As Range
>>> >
>>> > With Sheets("Analysis")
>>> > Set TradesEntered = .Range("at17:at56")
>>> > End With
>>> >
>>> >
>>> > 'Loop: Check for complete trades, copy to Trade History
>>> > For X = 1 To TradesEntered.Count
>>> > Set ClosCheck = TradesEntered(X)
>>> >
>>> > ' For Each PastCheck In TradesEnteredPast
>>> >
>>> >
>>> > If ClosCheck.Value = "True" Then
>>> > With ClosCheck
>>> > .Worksheet.Select
>>> > ClosCheck.EntireRow.Select
>>> > Selection.Copy
>>> > Sheets("TradeHistory").Select
>>> > Range("A4").Activate
>>> > Selection.End(xlDown).Select
>>> > ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
>>> > ActiveCell.EntireRow.Select
>>> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>>> > SkipBlanks _
>>> > :=False, Transpose:=False
>>> > Application.CutCopyMode = False
>>> > Range("A1").Select
>>> > Sheets("Analysis").Select
>>> > Range("A1").Select
>>> > End With
>>> > 'Else
>>> > ' MsgBox ("OK") 'Goes with Else. Comment out
>>> > ' Exit Sub 'Goes with Else. Comment it out.
>>> > End If
>>> > Next 'Ends "For Each" Loop
>>> > 'Unprotect, Unhide TradeHistory & Analysis Sheets
>>> >
>>> > THIS IS WHERE I HAVE PROBLEMS—DO FAR EVERYTHING I TRIED EITHER DOESN’T WORK
>>> > OR ERRORS OUT.
>>> > 'Dim TradesEntered As Range, ClosCheck As Range
>>> > 'Sheets("Analysis").Select
>>> >
>>> > With ActiveSheet
>>> > 'remove the existing filter
>>> > .AutoFilterMode = False
>>> > .Range("ar17:ar56").AutoFilter field:=46, Criteria1:="True"
>>> > 'in case there are no visible cells
>>> > On Error Resume Next
>>> > .Range("A17:F56").SpecialCells(xlCellTypeVisible).ClearContents
>>> > .Range("K17:M56").SpecialCells(xlCellTypeVisible).ClearContents
>>> > .Range("O17:S56").SpecialCells(xlCellTypeVisible).ClearContents
>>> > On Error GoTo 0
>>> > .AutoFilterMode = False
>>> > End With
>>> >
>>>
>>>
>

Thanks Ron. I think you "unstuck" me

"Ron de Bruin" wrote:

> Oops
>
> Typo, use this
>
> .EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
>
> Instead of
> .Rows(Lrow).SpecialCells(xlCellTypeConstants).ClearContents
>
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Ron de Bruin" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> > Ok, test this on for the activesheet
> >
> > If column AT = "True" it will delete all constants in that row
> >
> > Sub Loop_Example()
> > Dim Firstrow As Long
> > Dim Lastrow As Long
> > Dim Lrow As Long
> > Dim CalcMode As Long
> > Dim ViewMode As Long
> >
> > With Application
> > CalcMode = .Calculation
> > .Calculation = xlCalculationManual
> > .ScreenUpdating = False
> > End With
> >
> > 'We use the ActiveSheet but you can replace this with
> > 'Sheets("MySheet")if you want
> > With ActiveSheet
> >
> > 'We select the sheet so we can change the window view
> > .Select
> >
> > 'If you are in Page Break Preview Or Page Layout view go
> > 'back to normal view, we do this for speed
> > ViewMode = ActiveWindow.View
> > ActiveWindow.View = xlNormalView
> >
> > 'Turn off Page Breaks, we do this for speed
> > .DisplayPageBreaks = False
> >
> > 'Set the first and last row to loop through
> > Firstrow = .UsedRange.Cells(1).Row
> > Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
> >
> > 'We loop from Lastrow to Firstrow (bottom to top)
> > For Lrow = Lastrow To Firstrow Step -1
> >
> > 'We check the values in the AT column in this example
> > With .Cells(Lrow, "AT")
> >
> > If Not IsError(.Value) Then
> >
> > If .Value = "True" Then
> > On Error Resume Next
> > .Rows(Lrow).SpecialCells(xlCellTypeConstants).ClearContents
> > On Error GoTo 0
> > End If
> >
> > End If
> >
> > End With
> >
> > Next Lrow
> >
> > End With
> >
> > ActiveWindow.View = ViewMode
> > With Application
> > .ScreenUpdating = True
> > .Calculation = CalcMode
> > End With
> >
> > End Sub
> >
> >
> > --
> >
> > Regards Ron de Bruin
> > http://www.rondebruin.nl/tips.htm
> >
> >
> > "Andyjim" <(E-Mail Removed)> wrote in message news:EEF14F25-936D-4DC0-8CA8-(E-Mail Removed)...
> >> Hi Ron-
> >> Yes. Every row has some columns with formulas. I need to delete only the
> >> cells in that row that do not have formulas. THey include a17:f56, k17:m56,
> >> and o17:s56 - everywhere column AT = the label "True". Thanks
> >>
> >> "Ron de Bruin" wrote:
> >>
> >>> Hi Andy
> >>>
> >>> If I understand you correct you only want to delete cells in the row with no formula.
> >>>
> >>>
> >>> --
> >>>
> >>> Regards Ron de Bruin
> >>> http://www.rondebruin.nl/tips.htm
> >>>
> >>>
> >>> "Andyjim" <(E-Mail Removed)> wrote in message news:B9897768-E30E-4572-B626-(E-Mail Removed)...
> >>> > Hello-
> >>> >
> >>> > I seem to have done a poor job explaining what I am trying to do. In order
> >>> > to be more clear I am submitting most of my entire macro.
> >>> >
> >>> > First I loop
> >>> > through the cell range a:17 to av:56 and copy all rows with the criteria in
> >>> > column AR = “True” *(a label) to another worksheet.. This loop works very
> >>> > well.
> >>> >
> >>> > Problem:
> >>> > Then I want to delete certain cells in the rows (the same rows that the
> >>> > first loop copied)–-only this time I want to delete certain cells in that
> >>> > row---not the
> >>> > entire row because many of the rows contain formulas.
> >>> >
> >>> > I noted below where the problem starts. Currently I have some code there,
> >>> > but it doesn’t do the job.I have tried both loops and auto filters. Cannot
> >>> > get either to work.
> >>> >
> >>> >
> >>> > I feel like this should be simple, but I’ve never been so stumped.
> >>> >
> >>> >
> >>> > Sub MoveCompletedTradesLoop()
> >>> >
> >>> > Application.Run "Unprotect"
> >>> > 'Define Variables
> >>> > Dim TradesEntered As Range, ClosCheck As Range
> >>> >
> >>> > With Sheets("Analysis")
> >>> > Set TradesEntered = .Range("at17:at56")
> >>> > End With
> >>> >
> >>> >
> >>> > 'Loop: Check for complete trades, copy to Trade History
> >>> > For X = 1 To TradesEntered.Count
> >>> > Set ClosCheck = TradesEntered(X)
> >>> >
> >>> > ' For Each PastCheck In TradesEnteredPast
> >>> >
> >>> >
> >>> > If ClosCheck.Value = "True" Then
> >>> > With ClosCheck
> >>> > .Worksheet.Select
> >>> > ClosCheck.EntireRow.Select
> >>> > Selection.Copy
> >>> > Sheets("TradeHistory").Select
> >>> > Range("A4").Activate
> >>> > Selection.End(xlDown).Select
> >>> > ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
> >>> > ActiveCell.EntireRow.Select
> >>> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> >>> > SkipBlanks _
> >>> > :=False, Transpose:=False
> >>> > Application.CutCopyMode = False
> >>> > Range("A1").Select
> >>> > Sheets("Analysis").Select
> >>> > Range("A1").Select
> >>> > End With
> >>> > 'Else
> >>> > ' MsgBox ("OK") 'Goes with Else. Comment out
> >>> > ' Exit Sub 'Goes with Else. Comment it out.
> >>> > End If
> >>> > Next 'Ends "For Each" Loop
> >>> > 'Unprotect, Unhide TradeHistory & Analysis Sheets
> >>> >
> >>> > THIS IS WHERE I HAVE PROBLEMS—DO FAR EVERYTHING I TRIED EITHER DOESN’T WORK
> >>> > OR ERRORS OUT.
> >>> > 'Dim TradesEntered As Range, ClosCheck As Range
> >>> > 'Sheets("Analysis").Select
> >>> >
> >>> > With ActiveSheet
> >>> > 'remove the existing filter
> >>> > .AutoFilterMode = False
> >>> > .Range("ar17:ar56").AutoFilter field:=46, Criteria1:="True"
> >>> > 'in case there are no visible cells
> >>> > On Error Resume Next
> >>> > .Range("A17:F56").SpecialCells(xlCellTypeVisible).ClearContents
> >>> > .Range("K17:M56").SpecialCells(xlCellTypeVisible).ClearContents
> >>> > .Range("O17:S56").SpecialCells(xlCellTypeVisible).ClearContents
> >>> > On Error GoTo 0
> >>> > .AutoFilterMode = False
> >>> > End With
> >>> >
> >>>
> >>>
> >
>
>

Hi

Why don't clear the cells at once, if the cell meets your criteria (after
copy)?

Btw: Using "Select" in every line is makeing your code very slow....

Sub MoveCompletedTradesLoop()

Application.Run "Unprotect"
'Define Variables
Dim TradesEntered As Range, ClosCheck As Range, X As Integer, r As Integer

With Sheets("Analysis")
Set TradesEntered = .Range("at17:at56")
End With

For X = 1 To TradesEntered.Count
Set ClosCheck = TradesEntered(X)
If ClosCheck.Value = "True" Then
With ClosCheck
.Worksheet.Select
ClosCheck.EntireRow.Copy

Sheets("TradeHistory").Select
Range("A65536").End(xlUp).Offset(1, 0).EntireRow.PasteSpecial
Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select ' Don't needed, just slows down the code.
Place it after the For...Next loop if needed

Sheets("Analysis").Select
r = ClosCheck.Row
Range(Cells(r, 1), Cells(r, 6)).ClearContents
Range(Cells(r, "K"), Cells(r, "M")).ClearContents
Range(Cells(r, "O"), Cells(r, "S")).ClearContents
Range("A1").Select 'See comment above
End With

End If
Next
End Sub

Regards,

Per


"Andyjim" <(E-Mail Removed)> skrev i en meddelelse
news:B9897768-E30E-4572-B626-(E-Mail Removed)...
> Hello-
>
> I seem to have done a poor job explaining what I am trying to do. In
> order
> to be more clear I am submitting most of my entire macro.
>
> First I loop
> through the cell range a:17 to av:56 and copy all rows with the criteria
> in
> column AR = "True" *(a label) to another worksheet.. This loop works very
> well.
>
> Problem:
> Then I want to delete certain cells in the rows (the same rows that the
> first loop copied)--only this time I want to delete certain cells in that
> row---not the
> entire row because many of the rows contain formulas.
>
> I noted below where the problem starts. Currently I have some code there,
> but it doesn't do the job.I have tried both loops and auto filters.
> Cannot
> get either to work.
>
>
> I feel like this should be simple, but I've never been so stumped.
>
>
> Sub MoveCompletedTradesLoop()
>
> Application.Run "Unprotect"
> 'Define Variables
> Dim TradesEntered As Range, ClosCheck As Range
>
> With Sheets("Analysis")
> Set TradesEntered = .Range("at17:at56")
> End With
>
>
> 'Loop: Check for complete trades, copy to Trade History
> For X = 1 To TradesEntered.Count
> Set ClosCheck = TradesEntered(X)
>
> ' For Each PastCheck In TradesEnteredPast
>
>
> If ClosCheck.Value = "True" Then
> With ClosCheck
> .Worksheet.Select
> ClosCheck.EntireRow.Select
> Selection.Copy
> Sheets("TradeHistory").Select
> Range("A4").Activate
> Selection.End(xlDown).Select
> ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
> ActiveCell.EntireRow.Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> Range("A1").Select
> Sheets("Analysis").Select
> Range("A1").Select
> End With
> 'Else
> ' MsgBox ("OK") 'Goes with Else. Comment out
> ' Exit Sub 'Goes with Else. Comment it out.
> End If
> Next 'Ends "For Each" Loop
> 'Unprotect, Unhide TradeHistory & Analysis Sheets
>
> THIS IS WHERE I HAVE PROBLEMS-DO FAR EVERYTHING I TRIED EITHER DOESN'T
> WORK
> OR ERRORS OUT.
> 'Dim TradesEntered As Range, ClosCheck As Range
> 'Sheets("Analysis").Select
>
> With ActiveSheet
> 'remove the existing filter
> .AutoFilterMode = False
> .Range("ar17:ar56").AutoFilter field:=46, Criteria1:="True"
> 'in case there are no visible cells
> On Error Resume Next
> .Range("A17:F56").SpecialCells(xlCellTypeVisible).ClearContents
> .Range("K17:M56").SpecialCells(xlCellTypeVisible).ClearContents
> .Range("O17:S56").SpecialCells(xlCellTypeVisible).ClearContents
> On Error GoTo 0
> .AutoFilterMode = False
> End With
>

Hi Andy

>unstuck

I not know this word
Is it working for you are not (Sorry I am Dutch)

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Andyjim" <(E-Mail Removed)> wrote in message news:BA3E55CB-53A4-4681-AC5C-(E-Mail Removed)...
> Thanks Ron. I think you "unstuck" me
>
> "Ron de Bruin" wrote:
>
>> Oops
>>
>> Typo, use this
>>
>> .EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
>>
>> Instead of
>> .Rows(Lrow).SpecialCells(xlCellTypeConstants).ClearContents
>>
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Ron de Bruin" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>> > Ok, test this on for the activesheet
>> >
>> > If column AT = "True" it will delete all constants in that row
>> >
>> > Sub Loop_Example()
>> > Dim Firstrow As Long
>> > Dim Lastrow As Long
>> > Dim Lrow As Long
>> > Dim CalcMode As Long
>> > Dim ViewMode As Long
>> >
>> > With Application
>> > CalcMode = .Calculation
>> > .Calculation = xlCalculationManual
>> > .ScreenUpdating = False
>> > End With
>> >
>> > 'We use the ActiveSheet but you can replace this with
>> > 'Sheets("MySheet")if you want
>> > With ActiveSheet
>> >
>> > 'We select the sheet so we can change the window view
>> > .Select
>> >
>> > 'If you are in Page Break Preview Or Page Layout view go
>> > 'back to normal view, we do this for speed
>> > ViewMode = ActiveWindow.View
>> > ActiveWindow.View = xlNormalView
>> >
>> > 'Turn off Page Breaks, we do this for speed
>> > .DisplayPageBreaks = False
>> >
>> > 'Set the first and last row to loop through
>> > Firstrow = .UsedRange.Cells(1).Row
>> > Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
>> >
>> > 'We loop from Lastrow to Firstrow (bottom to top)
>> > For Lrow = Lastrow To Firstrow Step -1
>> >
>> > 'We check the values in the AT column in this example
>> > With .Cells(Lrow, "AT")
>> >
>> > If Not IsError(.Value) Then
>> >
>> > If .Value = "True" Then
>> > On Error Resume Next
>> > .Rows(Lrow).SpecialCells(xlCellTypeConstants).ClearContents
>> > On Error GoTo 0
>> > End If
>> >
>> > End If
>> >
>> > End With
>> >
>> > Next Lrow
>> >
>> > End With
>> >
>> > ActiveWindow.View = ViewMode
>> > With Application
>> > .ScreenUpdating = True
>> > .Calculation = CalcMode
>> > End With
>> >
>> > End Sub
>> >
>> >
>> > --
>> >
>> > Regards Ron de Bruin
>> > http://www.rondebruin.nl/tips.htm
>> >
>> >
>> > "Andyjim" <(E-Mail Removed)> wrote in message news:EEF14F25-936D-4DC0-8CA8-(E-Mail Removed)...
>> >> Hi Ron-
>> >> Yes. Every row has some columns with formulas. I need to delete only the
>> >> cells in that row that do not have formulas. THey include a17:f56, k17:m56,
>> >> and o17:s56 - everywhere column AT = the label "True". Thanks
>> >>
>> >> "Ron de Bruin" wrote:
>> >>
>> >>> Hi Andy
>> >>>
>> >>> If I understand you correct you only want to delete cells in the row with no formula.
>> >>>
>> >>>
>> >>> --
>> >>>
>> >>> Regards Ron de Bruin
>> >>> http://www.rondebruin.nl/tips.htm
>> >>>
>> >>>
>> >>> "Andyjim" <(E-Mail Removed)> wrote in message news:B9897768-E30E-4572-B626-(E-Mail Removed)...
>> >>> > Hello-
>> >>> >
>> >>> > I seem to have done a poor job explaining what I am trying to do. In order
>> >>> > to be more clear I am submitting most of my entire macro.
>> >>> >
>> >>> > First I loop
>> >>> > through the cell range a:17 to av:56 and copy all rows with the criteria in
>> >>> > column AR = “True” *(a label) to another worksheet.. This loop works very
>> >>> > well.
>> >>> >
>> >>> > Problem:
>> >>> > Then I want to delete certain cells in the rows (the same rows that the
>> >>> > first loop copied)–-only this time I want to delete certain cells in that
>> >>> > row---not the
>> >>> > entire row because many of the rows contain formulas.
>> >>> >
>> >>> > I noted below where the problem starts. Currently I have some code there,
>> >>> > but it doesn’t do the job.I have tried both loops and auto filters. Cannot
>> >>> > get either to work.
>> >>> >
>> >>> >
>> >>> > I feel like this should be simple, but I’ve never been so stumped.
>> >>> >
>> >>> >
>> >>> > Sub MoveCompletedTradesLoop()
>> >>> >
>> >>> > Application.Run "Unprotect"
>> >>> > 'Define Variables
>> >>> > Dim TradesEntered As Range, ClosCheck As Range
>> >>> >
>> >>> > With Sheets("Analysis")
>> >>> > Set TradesEntered = .Range("at17:at56")
>> >>> > End With
>> >>> >
>> >>> >
>> >>> > 'Loop: Check for complete trades, copy to Trade History
>> >>> > For X = 1 To TradesEntered.Count
>> >>> > Set ClosCheck = TradesEntered(X)
>> >>> >
>> >>> > ' For Each PastCheck In TradesEnteredPast
>> >>> >
>> >>> >
>> >>> > If ClosCheck.Value = "True" Then
>> >>> > With ClosCheck
>> >>> > .Worksheet.Select
>> >>> > ClosCheck.EntireRow.Select
>> >>> > Selection.Copy
>> >>> > Sheets("TradeHistory").Select
>> >>> > Range("A4").Activate
>> >>> > Selection.End(xlDown).Select
>> >>> > ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
>> >>> > ActiveCell.EntireRow.Select
>> >>> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>> >>> > SkipBlanks _
>> >>> > :=False, Transpose:=False
>> >>> > Application.CutCopyMode = False
>> >>> > Range("A1").Select
>> >>> > Sheets("Analysis").Select
>> >>> > Range("A1").Select
>> >>> > End With
>> >>> > 'Else
>> >>> > ' MsgBox ("OK") 'Goes with Else. Comment out
>> >>> > ' Exit Sub 'Goes with Else. Comment it out.
>> >>> > End If
>> >>> > Next 'Ends "For Each" Loop
>> >>> > 'Unprotect, Unhide TradeHistory & Analysis Sheets
>> >>> >
>> >>> > THIS IS WHERE I HAVE PROBLEMS—DO FAR EVERYTHING I TRIED EITHER DOESN’T WORK
>> >>> > OR ERRORS OUT.
>> >>> > 'Dim TradesEntered As Range, ClosCheck As Range
>> >>> > 'Sheets("Analysis").Select
>> >>> >
>> >>> > With ActiveSheet
>> >>> > 'remove the existing filter
>> >>> > .AutoFilterMode = False
>> >>> > .Range("ar17:ar56").AutoFilter field:=46, Criteria1:="True"
>> >>> > 'in case there are no visible cells
>> >>> > On Error Resume Next
>> >>> > .Range("A17:F56").SpecialCells(xlCellTypeVisible).ClearContents
>> >>> > .Range("K17:M56").SpecialCells(xlCellTypeVisible).ClearContents
>> >>> > .Range("O17:S56").SpecialCells(xlCellTypeVisible).ClearContents
>> >>> > On Error GoTo 0
>> >>> > .AutoFilterMode = False
>> >>> > End With
>> >>> >
>> >>>
>> >>>
>> >
>>
>>

Unstuck: To be freed from a binding substance. (Like screwed up code)

"Ron de Bruin" wrote:

> Hi Andy
>
> >unstuck
>
> I not know this word
> Is it working for you are not (Sorry I am Dutch)
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Andyjim" <(E-Mail Removed)> wrote in message news:BA3E55CB-53A4-4681-AC5C-(E-Mail Removed)...
> > Thanks Ron. I think you "unstuck" me
> >
> > "Ron de Bruin" wrote:
> >
> >> Oops
> >>
> >> Typo, use this
> >>
> >> .EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
> >>
> >> Instead of
> >> .Rows(Lrow).SpecialCells(xlCellTypeConstants).ClearContents
> >>
> >>
> >>
> >> --
> >>
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl/tips.htm
> >>
> >>
> >> "Ron de Bruin" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> >> > Ok, test this on for the activesheet
> >> >
> >> > If column AT = "True" it will delete all constants in that row
> >> >
> >> > Sub Loop_Example()
> >> > Dim Firstrow As Long
> >> > Dim Lastrow As Long
> >> > Dim Lrow As Long
> >> > Dim CalcMode As Long
> >> > Dim ViewMode As Long
> >> >
> >> > With Application
> >> > CalcMode = .Calculation
> >> > .Calculation = xlCalculationManual
> >> > .ScreenUpdating = False
> >> > End With
> >> >
> >> > 'We use the ActiveSheet but you can replace this with
> >> > 'Sheets("MySheet")if you want
> >> > With ActiveSheet
> >> >
> >> > 'We select the sheet so we can change the window view
> >> > .Select
> >> >
> >> > 'If you are in Page Break Preview Or Page Layout view go
> >> > 'back to normal view, we do this for speed
> >> > ViewMode = ActiveWindow.View
> >> > ActiveWindow.View = xlNormalView
> >> >
> >> > 'Turn off Page Breaks, we do this for speed
> >> > .DisplayPageBreaks = False
> >> >
> >> > 'Set the first and last row to loop through
> >> > Firstrow = .UsedRange.Cells(1).Row
> >> > Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
> >> >
> >> > 'We loop from Lastrow to Firstrow (bottom to top)
> >> > For Lrow = Lastrow To Firstrow Step -1
> >> >
> >> > 'We check the values in the AT column in this example
> >> > With .Cells(Lrow, "AT")
> >> >
> >> > If Not IsError(.Value) Then
> >> >
> >> > If .Value = "True" Then
> >> > On Error Resume Next
> >> > .Rows(Lrow).SpecialCells(xlCellTypeConstants).ClearContents
> >> > On Error GoTo 0
> >> > End If
> >> >
> >> > End If
> >> >
> >> > End With
> >> >
> >> > Next Lrow
> >> >
> >> > End With
> >> >
> >> > ActiveWindow.View = ViewMode
> >> > With Application
> >> > .ScreenUpdating = True
> >> > .Calculation = CalcMode
> >> > End With
> >> >
> >> > End Sub
> >> >
> >> >
> >> > --
> >> >
> >> > Regards Ron de Bruin
> >> > http://www.rondebruin.nl/tips.htm
> >> >
> >> >
> >> > "Andyjim" <(E-Mail Removed)> wrote in message news:EEF14F25-936D-4DC0-8CA8-(E-Mail Removed)...
> >> >> Hi Ron-
> >> >> Yes. Every row has some columns with formulas. I need to delete only the
> >> >> cells in that row that do not have formulas. THey include a17:f56, k17:m56,
> >> >> and o17:s56 - everywhere column AT = the label "True". Thanks
> >> >>
> >> >> "Ron de Bruin" wrote:
> >> >>
> >> >>> Hi Andy
> >> >>>
> >> >>> If I understand you correct you only want to delete cells in the row with no formula.
> >> >>>
> >> >>>
> >> >>> --
> >> >>>
> >> >>> Regards Ron de Bruin
> >> >>> http://www.rondebruin.nl/tips.htm
> >> >>>
> >> >>>
> >> >>> "Andyjim" <(E-Mail Removed)> wrote in message news:B9897768-E30E-4572-B626-(E-Mail Removed)...
> >> >>> > Hello-
> >> >>> >
> >> >>> > I seem to have done a poor job explaining what I am trying to do. In order
> >> >>> > to be more clear I am submitting most of my entire macro.
> >> >>> >
> >> >>> > First I loop
> >> >>> > through the cell range a:17 to av:56 and copy all rows with the criteria in
> >> >>> > column AR = “True” *(a label) to another worksheet.. This loop works very
> >> >>> > well.
> >> >>> >
> >> >>> > Problem:
> >> >>> > Then I want to delete certain cells in the rows (the same rows that the
> >> >>> > first loop copied)–-only this time I want to delete certain cells in that
> >> >>> > row---not the
> >> >>> > entire row because many of the rows contain formulas.
> >> >>> >
> >> >>> > I noted below where the problem starts. Currently I have some code there,
> >> >>> > but it doesn’t do the job.I have tried both loops and auto filters. Cannot
> >> >>> > get either to work.
> >> >>> >
> >> >>> >
> >> >>> > I feel like this should be simple, but I’ve never been so stumped.
> >> >>> >
> >> >>> >
> >> >>> > Sub MoveCompletedTradesLoop()
> >> >>> >
> >> >>> > Application.Run "Unprotect"
> >> >>> > 'Define Variables
> >> >>> > Dim TradesEntered As Range, ClosCheck As Range
> >> >>> >
> >> >>> > With Sheets("Analysis")
> >> >>> > Set TradesEntered = .Range("at17:at56")
> >> >>> > End With
> >> >>> >
> >> >>> >
> >> >>> > 'Loop: Check for complete trades, copy to Trade History
> >> >>> > For X = 1 To TradesEntered.Count
> >> >>> > Set ClosCheck = TradesEntered(X)
> >> >>> >
> >> >>> > ' For Each PastCheck In TradesEnteredPast
> >> >>> >
> >> >>> >
> >> >>> > If ClosCheck.Value = "True" Then
> >> >>> > With ClosCheck
> >> >>> > .Worksheet.Select
> >> >>> > ClosCheck.EntireRow.Select
> >> >>> > Selection.Copy
> >> >>> > Sheets("TradeHistory").Select
> >> >>> > Range("A4").Activate
> >> >>> > Selection.End(xlDown).Select
> >> >>> > ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
> >> >>> > ActiveCell.EntireRow.Select
> >> >>> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> >> >>> > SkipBlanks _
> >> >>> > :=False, Transpose:=False
> >> >>> > Application.CutCopyMode = False
> >> >>> > Range("A1").Select
> >> >>> > Sheets("Analysis").Select
> >> >>> > Range("A1").Select
> >> >>> > End With
> >> >>> > 'Else
> >> >>> > ' MsgBox ("OK") 'Goes with Else. Comment out
> >> >>> > ' Exit Sub 'Goes with Else. Comment it out.
> >> >>> > End If
> >> >>> > Next 'Ends "For Each" Loop
> >> >>> > 'Unprotect, Unhide TradeHistory & Analysis Sheets
> >> >>> >
> >> >>> > THIS IS WHERE I HAVE PROBLEMS—DO FAR EVERYTHING I TRIED EITHER DOESN’T WORK
> >> >>> > OR ERRORS OUT.
> >> >>> > 'Dim TradesEntered As Range, ClosCheck As Range
> >> >>> > 'Sheets("Analysis").Select
> >> >>> >
> >> >>> > With ActiveSheet
> >> >>> > 'remove the existing filter
> >> >>> > .AutoFilterMode = False
> >> >>> > .Range("ar17:ar56").AutoFilter field:=46, Criteria1:="True"
> >> >>> > 'in case there are no visible cells
> >> >>> > On Error Resume Next
> >> >>> > .Range("A17:F56").SpecialCells(xlCellTypeVisible).ClearContents
> >> >>> > .Range("K17:M56").SpecialCells(xlCellTypeVisible).ClearContents
> >> >>> > .Range("O17:S56").SpecialCells(xlCellTypeVisible).ClearContents
> >> >>> > On Error GoTo 0
> >> >>> > .AutoFilterMode = False
> >> >>> > End With
> >> >>> >
> >> >>>
> >> >>>
> >> >
> >>
> >>
>
>

Thanks

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"JLGWhiz" <(E-Mail Removed)> wrote in message news:81D6B53E-7BFD-4B85-A5AC-(E-Mail Removed)...
> Unstuck: To be freed from a binding substance. (Like screwed up code)
>
> "Ron de Bruin" wrote:
>
>> Hi Andy
>>
>> >unstuck
>>
>> I not know this word
>> Is it working for you are not (Sorry I am Dutch)
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Andyjim" <(E-Mail Removed)> wrote in message news:BA3E55CB-53A4-4681-AC5C-(E-Mail Removed)...
>> > Thanks Ron. I think you "unstuck" me
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Oops
>> >>
>> >> Typo, use this
>> >>
>> >> .EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
>> >>
>> >> Instead of
>> >> .Rows(Lrow).SpecialCells(xlCellTypeConstants).ClearContents
>> >>
>> >>
>> >>
>> >> --
>> >>
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl/tips.htm
>> >>
>> >>
>> >> "Ron de Bruin" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>> >> > Ok, test this on for the activesheet
>> >> >
>> >> > If column AT = "True" it will delete all constants in that row
>> >> >
>> >> > Sub Loop_Example()
>> >> > Dim Firstrow As Long
>> >> > Dim Lastrow As Long
>> >> > Dim Lrow As Long
>> >> > Dim CalcMode As Long
>> >> > Dim ViewMode As Long
>> >> >
>> >> > With Application
>> >> > CalcMode = .Calculation
>> >> > .Calculation = xlCalculationManual
>> >> > .ScreenUpdating = False
>> >> > End With
>> >> >
>> >> > 'We use the ActiveSheet but you can replace this with
>> >> > 'Sheets("MySheet")if you want
>> >> > With ActiveSheet
>> >> >
>> >> > 'We select the sheet so we can change the window view
>> >> > .Select
>> >> >
>> >> > 'If you are in Page Break Preview Or Page Layout view go
>> >> > 'back to normal view, we do this for speed
>> >> > ViewMode = ActiveWindow.View
>> >> > ActiveWindow.View = xlNormalView
>> >> >
>> >> > 'Turn off Page Breaks, we do this for speed
>> >> > .DisplayPageBreaks = False
>> >> >
>> >> > 'Set the first and last row to loop through
>> >> > Firstrow = .UsedRange.Cells(1).Row
>> >> > Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
>> >> >
>> >> > 'We loop from Lastrow to Firstrow (bottom to top)
>> >> > For Lrow = Lastrow To Firstrow Step -1
>> >> >
>> >> > 'We check the values in the AT column in this example
>> >> > With .Cells(Lrow, "AT")
>> >> >
>> >> > If Not IsError(.Value) Then
>> >> >
>> >> > If .Value = "True" Then
>> >> > On Error Resume Next
>> >> > .Rows(Lrow).SpecialCells(xlCellTypeConstants).ClearContents
>> >> > On Error GoTo 0
>> >> > End If
>> >> >
>> >> > End If
>> >> >
>> >> > End With
>> >> >
>> >> > Next Lrow
>> >> >
>> >> > End With
>> >> >
>> >> > ActiveWindow.View = ViewMode
>> >> > With Application
>> >> > .ScreenUpdating = True
>> >> > .Calculation = CalcMode
>> >> > End With
>> >> >
>> >> > End Sub
>> >> >
>> >> >
>> >> > --
>> >> >
>> >> > Regards Ron de Bruin
>> >> > http://www.rondebruin.nl/tips.htm
>> >> >
>> >> >
>> >> > "Andyjim" <(E-Mail Removed)> wrote in message news:EEF14F25-936D-4DC0-8CA8-(E-Mail Removed)...
>> >> >> Hi Ron-
>> >> >> Yes. Every row has some columns with formulas. I need to delete only the
>> >> >> cells in that row that do not have formulas. THey include a17:f56, k17:m56,
>> >> >> and o17:s56 - everywhere column AT = the label "True". Thanks
>> >> >>
>> >> >> "Ron de Bruin" wrote:
>> >> >>
>> >> >>> Hi Andy
>> >> >>>
>> >> >>> If I understand you correct you only want to delete cells in the row with no formula.
>> >> >>>
>> >> >>>
>> >> >>> --
>> >> >>>
>> >> >>> Regards Ron de Bruin
>> >> >>> http://www.rondebruin.nl/tips.htm
>> >> >>>
>> >> >>>
>> >> >>> "Andyjim" <(E-Mail Removed)> wrote in message news:B9897768-E30E-4572-B626-(E-Mail Removed)...
>> >> >>> > Hello-
>> >> >>> >
>> >> >>> > I seem to have done a poor job explaining what I am trying to do. In order
>> >> >>> > to be more clear I am submitting most of my entire macro.
>> >> >>> >
>> >> >>> > First I loop
>> >> >>> > through the cell range a:17 to av:56 and copy all rows with the criteria in
>> >> >>> > column AR = “True” *(a label) to another worksheet.. This loop works very
>> >> >>> > well.
>> >> >>> >
>> >> >>> > Problem:
>> >> >>> > Then I want to delete certain cells in the rows (the same rows that the
>> >> >>> > first loop copied)–-only this time I want to delete certain cells in that
>> >> >>> > row---not the
>> >> >>> > entire row because many of the rows contain formulas.
>> >> >>> >
>> >> >>> > I noted below where the problem starts. Currently I have some code there,
>> >> >>> > but it doesn’t do the job.I have tried both loops and auto filters. Cannot
>> >> >>> > get either to work.
>> >> >>> >
>> >> >>> >
>> >> >>> > I feel like this should be simple, but I’ve never been so stumped.
>> >> >>> >
>> >> >>> >
>> >> >>> > Sub MoveCompletedTradesLoop()
>> >> >>> >
>> >> >>> > Application.Run "Unprotect"
>> >> >>> > 'Define Variables
>> >> >>> > Dim TradesEntered As Range, ClosCheck As Range
>> >> >>> >
>> >> >>> > With Sheets("Analysis")
>> >> >>> > Set TradesEntered = .Range("at17:at56")
>> >> >>> > End With
>> >> >>> >
>> >> >>> >
>> >> >>> > 'Loop: Check for complete trades, copy to Trade History
>> >> >>> > For X = 1 To TradesEntered.Count
>> >> >>> > Set ClosCheck = TradesEntered(X)
>> >> >>> >
>> >> >>> > ' For Each PastCheck In TradesEnteredPast
>> >> >>> >
>> >> >>> >
>> >> >>> > If ClosCheck.Value = "True" Then
>> >> >>> > With ClosCheck
>> >> >>> > .Worksheet.Select
>> >> >>> > ClosCheck.EntireRow.Select
>> >> >>> > Selection.Copy
>> >> >>> > Sheets("TradeHistory").Select
>> >> >>> > Range("A4").Activate
>> >> >>> > Selection.End(xlDown).Select
>> >> >>> > ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
>> >> >>> > ActiveCell.EntireRow.Select
>> >> >>> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>> >> >>> > SkipBlanks _
>> >> >>> > :=False, Transpose:=False
>> >> >>> > Application.CutCopyMode = False
>> >> >>> > Range("A1").Select
>> >> >>> > Sheets("Analysis").Select
>> >> >>> > Range("A1").Select
>> >> >>> > End With
>> >> >>> > 'Else
>> >> >>> > ' MsgBox ("OK") 'Goes with Else. Comment out
>> >> >>> > ' Exit Sub 'Goes with Else. Comment it out.
>> >> >>> > End If
>> >> >>> > Next 'Ends "For Each" Loop
>> >> >>> > 'Unprotect, Unhide TradeHistory & Analysis Sheets
>> >> >>> >
>> >> >>> > THIS IS WHERE I HAVE PROBLEMS—DO FAR EVERYTHING I TRIED EITHER DOESN’T WORK
>> >> >>> > OR ERRORS OUT.
>> >> >>> > 'Dim TradesEntered As Range, ClosCheck As Range
>> >> >>> > 'Sheets("Analysis").Select
>> >> >>> >
>> >> >>> > With ActiveSheet
>> >> >>> > 'remove the existing filter
>> >> >>> > .AutoFilterMode = False
>> >> >>> > .Range("ar17:ar56").AutoFilter field:=46, Criteria1:="True"
>> >> >>> > 'in case there are no visible cells
>> >> >>> > On Error Resume Next
>> >> >>> > .Range("A17:F56").SpecialCells(xlCellTypeVisible).ClearContents
>> >> >>> > .Range("K17:M56").SpecialCells(xlCellTypeVisible).ClearContents
>> >> >>> > .Range("O17:S56").SpecialCells(xlCellTypeVisible).ClearContents
>> >> >>> > On Error GoTo 0
>> >> >>> > .AutoFilterMode = False
>> >> >>> > End With
>> >> >>> >
>> >> >>>
>> >> >>>
>> >> >
>> >>
>> >>
>>
>>