Deleting cells in a row

[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

 

[Ron de Bruin]

Hi Andy

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

 

[Andyjim]

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]

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

 

[Ron de Bruin]

Oops

Typo, use this

.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

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

 

[Andyjim]

Thanks Ron. I think you "unstuck" me

Oops

Typo, use this

.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

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

 

[Per Jessen]

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

 

[Ron de Bruin]

Hi Andy

unstuck

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

 

[JLGWhiz]

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

 

[Ron de Bruin]

Thanks