1

I am working on a spreadsheet that utilizes columns A:AV. I have it set to automatically copy and paste the entire row when a cell in column D has the text "Closed". I couldn't cut the row due to formatting that the sheet has, so instead I'm using this to clear the row Target.EntireRow.Value = "". My question is, is it possible to set the values of the entire row to "" while skipping over columns W and X? I have formulas in those columns that I don't want to be erased. Thanks for the help.

Andy
  • 15
  • 3
  • Word of advice, I would not use `Value = ""` that does not clear the cells just puts a blank string in it. Use `Value = vbNullString` – anonymous Feb 15 '17 at 21:11
  • Also, you could use a loop to clear each cell and add in `If` statements to skip over W and X – anonymous Feb 15 '17 at 21:13
  • Thanks for the advice! – Andy Feb 15 '17 at 21:45
  • @SnWhte - can you provide some sort of test to show the difference of a cell that has had its `Value` set to `""` versus one that has had its `Value` set to `vbNullString`. (I'm struggling to find one - all the tests I have tried in both VBA and Excel think they are both `Empty`.) – YowE3K Feb 16 '17 at 02:28
  • You won't see a difference it is more so for optimization and efficiency. If you use "" more rows will be in "use" than if you use `vbNullString`. Check out the answer [here](http://stackoverflow.com/questions/32435320/is-there-any-difference-between-vbnullstring-and) and the link they provide in the answer [here](http://www.aivosto.com/vbtips/stringopt.html) – anonymous Feb 16 '17 at 15:09
  • @YowE3K see comment above, didn't realize you weren't OP – anonymous Feb 16 '17 at 16:56
  • @SnWhte - I can understand the efficiency issues (the compiler can point to a constant null string instead of creating a new instance of one), but using `""` or `vbNullString` seems to give exactly the same effect on `UsedRange` (and everything else as far as Excel is concerned). I admit to being surprised when I saw the behaviour - I expected **both** methods would leave a null string in the cell, and was not expecting it to be the same as doing a `ClearContents` - but that appears to be exactly what it is doing. – YowE3K Feb 16 '17 at 18:41

1 Answers1

2

To clear all cells in a row except for columns W and X:

With Target.Parent
    Union(.Range(.Cells(Target.Row, "A"), .Cells(Target.Row, "V")), _
          .Range(.Cells(Target.Row, "Y"), .Cells(Target.Row, .Columns.Count))).ClearContents
End With
tigeravatar
  • 26,199
  • 5
  • 30
  • 38