Posted to microsoft.public.excel.programming
|
|
Deleting cells in a row
Thanks
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"JLGWhiz" wrote in message ...
Unstuck: To be freed from a binding substance. (Like screwed up code)
"Ron de Bruin" wrote:
Hi Andy
unstuck
I not know this word
Is it working for you are not (Sorry I am Dutch)
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Andyjim" wrote in message ...
Thanks Ron. I think you "unstuck" me
"Ron de Bruin" wrote:
Oops
Typo, use this
.EntireRow.SpecialCells(xlCellTypeConstants).Clear Contents
Instead of
.Rows(Lrow).SpecialCells(xlCellTypeConstants).Clea rContents
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Ron de Bruin" wrote in message ...
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).Clea rContents
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
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Andyjim" wrote in message ...
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" wrote:
Hi Andy
If I understand you correct you only want to delete cells in the row with no formula.
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Andyjim" wrote in message ...
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 doesnt do the job.I have tried both loops and auto filters. Cannot
get either to work.
I feel like this should be simple, but Ive 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 DOESNT 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
|