deleting cells: Auto filter or loop or what

[Andyjim]

I have tried to accomplish deleting certain cells based on the criteria in
Column 46 labeled as TRUE. I have tried a loop and an auto filter but I am
not getting either to work. I also tried recording a macro using the auto
filter and still no luck (the code was slightly different than the code
below). I need to get this solved as soon as possible. You folks always
come through for us...sorry to be so impatient. Included is the code for
the auto filter and the code I tried for the loop:

auto filter:
Range("AR16:AR56").AutoFilter Field:=46, 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


Loop:

Dim clearrow

'Loop: Check for complete trades, delete

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


If ClosCheck.Value = "True" Then
With ClosCheck

clearrow = ActiveCell.Row
Range("A17" & clearrow & ":F56" & clearrow).Value = ""
Range("K17" & clearrow & ":M56" & clearrow).Value = ""
Range("O17" & clearrow & ":S56" & clearrow).Value = ""
End With

End If
Next

 

[Dave Peterson]

I'm confused over what you really want. You have AR16:AR56, but then include
rows.count.

So maybe...

With ActiveSheet
'remove the existing filter
.AutoFilterMode = False
.Range("ar16:ar56").AutoFilter field:=1, 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

or

Dim LastRow As Long
With ActiveSheet
'remove the existing filter
.AutoFilterMode = False
LastRow = .Cells(.Rows.Count, "AR").End(xlUp).Row
.Range("ar16:ar" & LastRow).AutoFilter field:=1, Criteria1:=True
'in case there are no visible cells
On Error Resume Next
.Range("A17:F" & LastRow).SpecialCells(xlCellTypeVisible).ClearContents
.Range("K17:M" & LastRow).SpecialCells(xlCellTypeVisible).ClearContents
.Range("O17:S" & LastRow).SpecialCells(xlCellTypeVisible).ClearContents
On Error GoTo 0
.AutoFilterMode = False
End With

I used column AR to find the last row.

 

[Andyjim]

Sorry I’m not explaining myself. Below is the entire code. 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..
Then I want to delete certain cells in the rows that were copied –not the
entire row because many of the rows contain formulas.
I feel like this should be simple, but I’ve never been so stumped.

I included the code you gave me changing the Field criteria from 1 to 46. I
hope this gives you a better picture:

Thanks Dave...

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


'Dim TradesEntered As Range, ClosCheck As Range
'Sheets("Analysis").Select

With ActiveSheet
'remove the existing filter
..AutoFilterMode = False
..Range("ar16: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

 

[Andyjim]

Dave-
I forgot to mention--neither the code for the auto filter or loop you gave
me seems to work as i have it. I hope by giving you the entire macro, you
might be able to help me.

 

[Dave Peterson]

I didn't give you code for a loop in this thread.

And you changed my code.

I used:
..Range("ar16:ar56").AutoFilter field:=1, Criteria1:=True

You used:
..Range("ar16:ar56").AutoFilter field:=46, Criteria1:="True"

There aren't 46 fields in column AR--just one.

And I removed the double quotes around the True. I guessed that you had the
boolean TRUE in the cells--not the text True.

 

[Dave Peterson]

I see you started another (at least 3rd) thread.

I'll bow out.