| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting cells in a loop
I am trying to use a loop to select a particular criteria (i.e. col AR = True
and then delete certain cells in that row. It was suggested I use ..clearcontents but I encountered an error something like "can't change merged cell." I looked up a post that then suggested using .value = "" to handle this error. But the main reason for writing is that this loop isn't working. No cells are being deleted. Any help would be greatly appreciated. -Andy With Sheets("Analysis") Set TradesEntered = .Range("at17:at56") End With Dim clearrow 'Loop: Check for complete trades, delete For X = 1 To TradesEntered.Count Set ClosCheck = TradesEntered(X) ' For Each PastCheck In TradesEnteredPast If ClosCheck.Value = "True" Then With ClosCheck '.Worksheet.Select clearrow = ActiveCell.Row Range("A" & clearrow & ":F" & clearrow).Value = "" Range("K" & clearrow & ":M" & clearrow).Value = "" Range("O" & clearrow & ":S" & clearrow).Value = "" End With End If Next |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting cells in a loop
Andy,
No need to loop: this assumes that your header row is row 1, and the code will clear cells in columns A-F, K-M, O-S Sub Macro1() Range("AR:AR").AutoFilter Field:=1, Criteria1:="TRUE" Range("A2:F" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = "" Range("K2:M" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = "" Range("O2:S" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = "" Range("AR:AR").AutoFilter End Sub HTH, Bernie MS Excel MVP "Andyjim" wrote in message ... I am trying to use a loop to select a particular criteria (i.e. col AR = True and then delete certain cells in that row. It was suggested I use .clearcontents but I encountered an error something like "can't change merged cell." I looked up a post that then suggested using .value = "" to handle this error. But the main reason for writing is that this loop isn't working. No cells are being deleted. Any help would be greatly appreciated. -Andy With Sheets("Analysis") Set TradesEntered = .Range("at17:at56") End With Dim clearrow 'Loop: Check for complete trades, delete For X = 1 To TradesEntered.Count Set ClosCheck = TradesEntered(X) ' For Each PastCheck In TradesEnteredPast If ClosCheck.Value = "True" Then With ClosCheck '.Worksheet.Select clearrow = ActiveCell.Row Range("A" & clearrow & ":F" & clearrow).Value = "" Range("K" & clearrow & ":M" & clearrow).Value = "" Range("O" & clearrow & ":S" & clearrow).Value = "" End With End If Next |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting cells in a loop
Thank you for your quick respone, Bernie. I can't help but think we're
close, but here is the code I adapted from your input and here is the error I get: "Method 'range' of object '_global failed" Sheets("Analysis").Select Range("AR16:AR56").AutoFilter Field:=1, 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 This error occurs right after line wiht "TRUE" in it. "Bernie Deitrick" wrote: Andy, No need to loop: this assumes that your header row is row 1, and the code will clear cells in columns A-F, K-M, O-S Sub Macro1() Range("AR:AR").AutoFilter Field:=1, Criteria1:="TRUE" Range("A2:F" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = "" Range("K2:M" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = "" Range("O2:S" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = "" Range("AR:AR").AutoFilter End Sub HTH, Bernie MS Excel MVP "Andyjim" wrote in message ... I am trying to use a loop to select a particular criteria (i.e. col AR = True and then delete certain cells in that row. It was suggested I use .clearcontents but I encountered an error something like "can't change merged cell." I looked up a post that then suggested using .value = "" to handle this error. But the main reason for writing is that this loop isn't working. No cells are being deleted. Any help would be greatly appreciated. -Andy With Sheets("Analysis") Set TradesEntered = .Range("at17:at56") End With Dim clearrow 'Loop: Check for complete trades, delete For X = 1 To TradesEntered.Count Set ClosCheck = TradesEntered(X) ' For Each PastCheck In TradesEnteredPast If ClosCheck.Value = "True" Then With ClosCheck '.Worksheet.Select clearrow = ActiveCell.Row Range("A" & clearrow & ":F" & clearrow).Value = "" Range("K" & clearrow & ":M" & clearrow).Value = "" Range("O" & clearrow & ":S" & clearrow).Value = "" End With End If Next |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting cells in a loop
Bernie- Now I get error on the line with the TRUE statement. Autofilter method of range class failed All I think I did was change the code from FIeld: 1 to Field 46 (where criteria True exists) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting cells in a loop
Jim,
The error is due to all those 56's that you threw in. Try it this way. Dim myRow As Long Sheets("Analysis").Select myRow = 56 'If you want to pick up all the rows, use code like this 'myRow = Cells(Rows.Count, Range("AR1").Column).End(xlUp).Row Range("AR16:AR" & myRow).AutoFilter Field:=1, Criteria1:="TRUE" Range("A17:F" & myRow).SpecialCells(xlCellTypeVisible).Value = "" Range("K17:M" & myRow).SpecialCells(xlCellTypeVisible).Value = "" Range("O17:S" & myRow).SpecialCells(xlCellTypeVisible).Value = "" Range("AR16:AR" & myRow).AutoFilter HTH, Bernie MS Excel MVP "Andyjim" wrote in message ... Thank you for your quick respone, Bernie. I can't help but think we're close, but here is the code I adapted from your input and here is the error I get: "Method 'range' of object '_global failed" Sheets("Analysis").Select Range("AR16:AR56").AutoFilter Field:=1, 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 This error occurs right after line wiht "TRUE" in it. "Bernie Deitrick" wrote: Andy, No need to loop: this assumes that your header row is row 1, and the code will clear cells in columns A-F, K-M, O-S Sub Macro1() Range("AR:AR").AutoFilter Field:=1, Criteria1:="TRUE" Range("A2:F" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = "" Range("K2:M" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = "" Range("O2:S" & Rows.Count).SpecialCells(xlCellTypeVisible).Value = "" Range("AR:AR").AutoFilter End Sub HTH, Bernie MS Excel MVP "Andyjim" wrote in message ... I am trying to use a loop to select a particular criteria (i.e. col AR = True and then delete certain cells in that row. It was suggested I use .clearcontents but I encountered an error something like "can't change merged cell." I looked up a post that then suggested using .value = "" to handle this error. But the main reason for writing is that this loop isn't working. No cells are being deleted. Any help would be greatly appreciated. -Andy With Sheets("Analysis") Set TradesEntered = .Range("at17:at56") End With Dim clearrow 'Loop: Check for complete trades, delete For X = 1 To TradesEntered.Count Set ClosCheck = TradesEntered(X) ' For Each PastCheck In TradesEnteredPast If ClosCheck.Value = "True" Then With ClosCheck '.Worksheet.Select clearrow = ActiveCell.Row Range("A" & clearrow & ":F" & clearrow).Value = "" Range("K" & clearrow & ":M" & clearrow).Value = "" Range("O" & clearrow & ":S" & clearrow).Value = "" End With End If Next |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to continue loop after deleting row? | Excel Programming | |||
| Deleting rows loop | Excel Programming | |||
| deleting rows, endless loop maybe ? | Excel Programming | |||
| Help - loop through cells in a range that are not together (several different cells as Target) | Excel Programming | |||
| Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming | |||
Hybrid Mode
