A
Andyjim
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
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