Deleting cells in a loop

[Andyjim]

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 Deitrick]

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]

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.

 

[Andyjim]

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)

 

[Bernie Deitrick]

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

 

[Dave Peterson]

Check your other post, too.

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)