0

I would like to auto ClearContents for a cell based on another cell being blank.
enter image description here

I have the values in the "Tracker" sheet in column L, on which rely exactly the same rows in another sheet ("Formulas") in column AF.

When I remove manually the value from the cell L2 for example, I would like to auto Clear Contents for column AF2 and so on.

I found Clear Excel Cell if Value less than another Cell value
I tried the following:

Sub ClearLowerThan()
Dim c As Range, Rng As Range
Dim LastRow As Long
Dim ws As Worksheet, ws2 As Worksheet: Set ws = Sheets("Tracker")
Set ws2 = Sheets("Formulas")
'declare you worksheet, amend as required
LastRow = ws.Cells(ws.Rows.Count, "L").End(xlUp).Row

CompareVal = LastRow

'get the value to compare against, in this case from cell B1

Set Rng = ws2.Range("AF2:AF" & LastRow)
'set the range to compare against the value from B1

For Each c In Rng 'for each cell in the given range
    If c.value < CompareVal Then c.ClearContents
    'if value of cell is less than the value to compare against, clear the cell contents.
Next
End Sub

I saw no reaction. I know, that code refers to clear lower data than, but I don't know how to change it for my purpose.

In Excel: How to check if a cell is empty with VBA? I found the IsEmpty() function. I changed

  If c.value < CompareVal Then c.ClearContents

to

  If IsEmpty(CompareVal.value) Then c.ClearContents

I get an error: Object required with debugger pointing on this line.

How can I autodelete values for the cell in another sheet located on the same row?

Community
  • 1
  • 1
Geographos
  • 827
  • 2
  • 23
  • 57
  • Maybe you need to directly refer to the sheet containing the cell you would like to clear [Clear a range without activating the sheet](https://social.msdn.microsoft.com/Forums/office/en-US/09e675cb-35d3-4444-85fa-435a1f008f08/clear-a-range-without-activating-the-sheet?forum=exceldev) – Tomasz Paluch Jun 29 '20 at 11:42
  • `IsEmpty` is to be used on a range *object*, not a range's `.Value`. So in your failing example, remove the `.Value` : `If IsEmpty(CompareVal) Then c.ClearContents` – CLR Jun 29 '20 at 11:45
  • Your hint might be somewhat right. I am not getting at least the error anymore. However, removing the stuff from the cell L2 doesn't make the cell AF2 blank at once. – Geographos Jun 29 '20 at 11:51
  • 1
    Check my answer code, please... It does what (I understood) you need automatically, immediately after the cell clearing... – FaneDuru Jun 29 '20 at 11:55

1 Answers1

1

Please, copy the next code in the Tracker module sheet:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Target.Column = 12 Then Exit Sub 'it does work only for changes in column L:L
 Dim ws2 As Worksheet: Set ws2 = Sheets("Formulas")

 ws2.Range("AF" & Target.row).Value = Target.Value
End Sub
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • Yes exactly! It works perfect! How about when I put the value back to my L cell? Then the value in cell AF doesn't appear anymore? Is there a solution for it? – Geographos Jun 29 '20 at 14:34
  • 1
    It can be, if we prepare it... I will adapt the code after some hours, when I will be home. – FaneDuru Jun 29 '20 at 15:15
  • I will be grateful. Thanks a lot :) – Geographos Jun 29 '20 at 15:19
  • @MKR: Updated. Now, it mirrors the value of any changed cell in "Tracker" sheet, L:L column, to AF:AF column of "Formulas" sheet. You requested only cell clearing... – FaneDuru Jun 29 '20 at 16:11
  • OK, thank you so much. I think, that mirroring the values is enough for the time being. – Geographos Jun 30 '20 at 09:50