Clear Row Contents (Excluding Column A & N)

[tomexcel1]

Hi All

I have the following code that clears the contents of a row based on the text in cell G. However I need the code to clear contents of the row but exclude anything in Column A and N.

Sub ClearIncorrectRows()Dim lRw As Long, n As Long
lRw = Range("G" & Rows.Count).End(xlUp).Row
For n = 1 To lRw
     If Range("G" & n).Value = "DELETE ROW" Then Range("G" & n).EntireRow.ClearContents
Next n
End Sub

Any help would be most appreciated.

Thanks
Tom

 

[MickG]

Try something like this:-

Sub ClearIncorrectRows()
Dim lRw As Long, n As Long, Temp1 As String, Temp2 As String
lRw = Range("G" & Rows.Count).End(xlUp).Row
For n = 1 To lRw
     If Range("G" & n).Value = "DELETE ROW" Then
        Temp1 = Range("A" & n): Temp2 = Range("N" & n)
            Range("G" & n).EntireRow.ClearContents
        Range("A" & n) = Temp1: Range("N" & n) = Temp2
    End If
Next n
End Sub

 

[tomexcel1]

Hi MickG!

Thanks for your help, it works great for column A but still seams to delete whats in column N. Could it be something to do with the fact column N as a formula in it?

 

[Fluff]

How about

Sub clearrws()
   Range("A:A,N:N").EntireColumn.Hidden = True
   With Range("G1", Range("G" & Rows.Count).End(xlUp))
      .Replace "delete row", "=XXX", xlWhole, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).EntireRow.SpecialCells(xlVisible).ClearContents
   End With
   Range("A:A,N:N").EntireColumn.Hidden = False
End Sub

 

[MickG]

Try changing code as shown in red :-

 Temp1 = Range("A" & n): Temp2 = Range("N" & n[B][COLOR=#FF0000]).Formula[/COLOR][/B]

 

[tomexcel1]

Perfect! changing the code works a treat! Thank you so much

 

[Rick Rothstein]

Assuming you will run this when you know there is at least one "Delete Row" cell, here is another macro that should work...

[table="width: 500"]
[tr]
	[td]Sub ClearIncorrectRows()
  Columns("G").Replace "Delete Row", "#N/A", xlWhole, , False, , False, False
  Intersect(Range("B:M,O:" & Split(Columns(Columns.Count).Address, "$")(2)), Columns("G").SpecialCells(xlConstants, xlErrors).EntireRow).ClearContents
End Sub[/td]
[/tr]
[/table]

If you cannot be sure that there will be at least one "Delete Row" cell, then use this instead...

[table="width: 500"]
[tr]
	[td]Sub ClearIncorrectRows()
  Columns("G").Replace "Delete Row", "#N/A", xlWhole, , False, , False, False
  On Error Resume Next
  Intersect(Range("B:M,O:" & Split(Columns(Columns.Count).Address, "$")(2)), Columns("G").SpecialCells(xlConstants, xlErrors).EntireRow).ClearContents
  On Error GoTo 0
End Sub[/td]
[/tr]
[/table]

 

[billandrew]

MICK G.

This is great.

Question - Are you storing the data in Range A : N in Temp 1 & Temp 2 and then clearing the contents if the value in Column G is "DELETE ROW" then reapplying the stored data.