I am trying to use a loop to select a particular criteria (i.e. col AR = True
and then delete certain cells in that row. It was suggested I use
..clearcontents but I encountered an error something like "can't change merged
cell." I looked up a post that then suggested using .value = "" to handle
this error.

But the main reason for writing is that this loop isn't working. No cells
are being deleted. Any help would be greatly appreciated.

-Andy





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

'Loop: Check for complete trades, delete

For X = 1 To TradesEntered.Count
Set ClosCheck = TradesEntered(X)

' For Each PastCheck In TradesEnteredPast


If ClosCheck.Value = "True" Then
With ClosCheck
'.Worksheet.Select
clearrow = ActiveCell.Row
Range("A" & clearrow & ":F" & clearrow).Value = ""
Range("K" & clearrow & ":M" & clearrow).Value = ""
Range("O" & clearrow & ":S" & clearrow).Value = ""
End With

End If
Next

Andy,

No need to loop: this assumes that your header row is row 1, and the code will clear cells in
columns A-F, K-M, O-S

Sub Macro1()
Range("AR:AR").AutoFilter Field:=1, Criteria1:="TRUE"
Range("A2:F" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
Range("K2:M" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
Range("O2:S" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
Range("AR:AR").AutoFilter
End Sub

HTH,
Bernie
MS Excel MVP


"Andyjim" <(E-Mail Removed)> wrote in message
news:0E3CAF95-DD95-458E-98DD-(E-Mail Removed)...
>I am trying to use a loop to select a particular criteria (i.e. col AR = True
> and then delete certain cells in that row. It was suggested I use
> .clearcontents but I encountered an error something like "can't change merged
> cell." I looked up a post that then suggested using .value = "" to handle
> this error.
>
> But the main reason for writing is that this loop isn't working. No cells
> are being deleted. Any help would be greatly appreciated.
>
> -Andy
>
>
>
>
>
> With Sheets("Analysis")
> Set TradesEntered = .Range("at17:at56")
> End With
> Dim clearrow
>
> 'Loop: Check for complete trades, delete
>
> For X = 1 To TradesEntered.Count
> Set ClosCheck = TradesEntered(X)
>
> ' For Each PastCheck In TradesEnteredPast
>
>
> If ClosCheck.Value = "True" Then
> With ClosCheck
> '.Worksheet.Select
> clearrow = ActiveCell.Row
> Range("A" & clearrow & ":F" & clearrow).Value = ""
> Range("K" & clearrow & ":M" & clearrow).Value = ""
> Range("O" & clearrow & ":S" & clearrow).Value = ""
> End With
>
> End If
> Next

Thank you for your quick respone, Bernie. I can't help but think we're
close, but here is the code I adapted from your input and here is the error I
get:
"Method 'range' of object '_global failed"

Sheets("Analysis").Select
Range("AR16:AR56").AutoFilter Field:=1, Criteria1:="TRUE"
Range("A17:F56" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
Range("K17:M56" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
Range("O17:S56" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
Range("AR16:AR56").AutoFilter

This error occurs right after line wiht "TRUE" in it.

"Bernie Deitrick" wrote:

> Andy,
>
> No need to loop: this assumes that your header row is row 1, and the code will clear cells in
> columns A-F, K-M, O-S
>
> Sub Macro1()
> Range("AR:AR").AutoFilter Field:=1, Criteria1:="TRUE"
> Range("A2:F" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
> Range("K2:M" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
> Range("O2:S" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
> Range("AR:AR").AutoFilter
> End Sub
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Andyjim" <(E-Mail Removed)> wrote in message
> news:0E3CAF95-DD95-458E-98DD-(E-Mail Removed)...
> >I am trying to use a loop to select a particular criteria (i.e. col AR = True
> > and then delete certain cells in that row. It was suggested I use
> > .clearcontents but I encountered an error something like "can't change merged
> > cell." I looked up a post that then suggested using .value = "" to handle
> > this error.
> >
> > But the main reason for writing is that this loop isn't working. No cells
> > are being deleted. Any help would be greatly appreciated.
> >
> > -Andy
> >
> >
> >
> >
> >
> > With Sheets("Analysis")
> > Set TradesEntered = .Range("at17:at56")
> > End With
> > Dim clearrow
> >
> > 'Loop: Check for complete trades, delete
> >
> > For X = 1 To TradesEntered.Count
> > Set ClosCheck = TradesEntered(X)
> >
> > ' For Each PastCheck In TradesEnteredPast
> >
> >
> > If ClosCheck.Value = "True" Then
> > With ClosCheck
> > '.Worksheet.Select
> > clearrow = ActiveCell.Row
> > Range("A" & clearrow & ":F" & clearrow).Value = ""
> > Range("K" & clearrow & ":M" & clearrow).Value = ""
> > Range("O" & clearrow & ":S" & clearrow).Value = ""
> > End With
> >
> > End If
> > Next
>
>
>

Bernie-

Now I get error on the line with the TRUE statement.

Autofilter method of range class failed

All I think I did was change the code from FIeld: 1 to Field 46 (where
criteria True exists)

Jim,

The error is due to all those 56's that you threw in.
Try it this way.



Dim myRow As Long

Sheets("Analysis").Select

myRow = 56
'If you want to pick up all the rows, use code like this
'myRow = Cells(Rows.Count, Range("AR1").Column).End(xlUp).Row


Range("AR16:AR" & myRow).AutoFilter Field:=1, Criteria1:="TRUE"
Range("A17:F" & myRow).SpecialCells(xlCellTypeVisible).Value = ""
Range("K17:M" & myRow).SpecialCells(xlCellTypeVisible).Value = ""
Range("O17:S" & myRow).SpecialCells(xlCellTypeVisible).Value = ""
Range("AR16:AR" & myRow).AutoFilter

HTH,
Bernie
MS Excel MVP


"Andyjim" <(E-Mail Removed)> wrote in message
newsEB3DDCF-A24A-490B-9B66-(E-Mail Removed)...
> Thank you for your quick respone, Bernie. I can't help but think we're
> close, but here is the code I adapted from your input and here is the error I
> get:
> "Method 'range' of object '_global failed"
>
> Sheets("Analysis").Select
> Range("AR16:AR56").AutoFilter Field:=1, Criteria1:="TRUE"
> Range("A17:F56" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
> Range("K17:M56" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
> Range("O17:S56" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
> Range("AR16:AR56").AutoFilter
>
> This error occurs right after line wiht "TRUE" in it.
>
> "Bernie Deitrick" wrote:
>
>> Andy,
>>
>> No need to loop: this assumes that your header row is row 1, and the code will clear cells in
>> columns A-F, K-M, O-S
>>
>> Sub Macro1()
>> Range("AR:AR").AutoFilter Field:=1, Criteria1:="TRUE"
>> Range("A2:F" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
>> Range("K2:M" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
>> Range("O2:S" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = ""
>> Range("AR:AR").AutoFilter
>> End Sub
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Andyjim" <(E-Mail Removed)> wrote in message
>> news:0E3CAF95-DD95-458E-98DD-(E-Mail Removed)...
>> >I am trying to use a loop to select a particular criteria (i.e. col AR = True
>> > and then delete certain cells in that row. It was suggested I use
>> > .clearcontents but I encountered an error something like "can't change merged
>> > cell." I looked up a post that then suggested using .value = "" to handle
>> > this error.
>> >
>> > But the main reason for writing is that this loop isn't working. No cells
>> > are being deleted. Any help would be greatly appreciated.
>> >
>> > -Andy
>> >
>> >
>> >
>> >
>> >
>> > With Sheets("Analysis")
>> > Set TradesEntered = .Range("at17:at56")
>> > End With
>> > Dim clearrow
>> >
>> > 'Loop: Check for complete trades, delete
>> >
>> > For X = 1 To TradesEntered.Count
>> > Set ClosCheck = TradesEntered(X)
>> >
>> > ' For Each PastCheck In TradesEnteredPast
>> >
>> >
>> > If ClosCheck.Value = "True" Then
>> > With ClosCheck
>> > '.Worksheet.Select
>> > clearrow = ActiveCell.Row
>> > Range("A" & clearrow & ":F" & clearrow).Value = ""
>> > Range("K" & clearrow & ":M" & clearrow).Value = ""
>> > Range("O" & clearrow & ":S" & clearrow).Value = ""
>> > End With
>> >
>> > End If
>> > Next
>>
>>
>>

Check your other post, too.

Andyjim wrote:
>
> Bernie-
>
> Now I get error on the line with the TRUE statement.
>
> Autofilter method of range class failed
>
> All I think I did was change the code from FIeld: 1 to Field 46 (where
> criteria True exists)

--

Dave Peterson