| Home |
| Search |
| Today's Posts |
|
#1
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Deleting Cells or Rows of Cells | Excel Discussion (Misc queries) | |||
| deleting unused cells / getting rid of inactive cells | Excel Discussion (Misc queries) | |||
| Deleting Cells | Excel Programming | |||
| Deleting cells in a row | Excel Programming | |||
| Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming | |||
Linear Mode
