If Cell in Column C is Blank, Make Adjacent Cell in Column B Blank

[ryguy7272]

i am trying to identify all blanks in Column C, and for each blank, delete
any value in the same row in Column B. Here is my code:

Dim lr As Long
lr = Sheets("PivotSheet").Cells(Rows.Count, 1).End(xlUp).Row

For Each C In Sheets("PivotSheet").Range("C4:C" & lr)
If C.Value = "" Then
ActiveCell.Offset(, -1) = ""
End If
Next C

What am I doing wrong?

Thanks,
Ryan---

 

[FSt1]

hi
i don't see where you are doing any selecting so.... you may not have an
activecell or your active cell is somewhere you don't think it is.
posible solution.
instead of
activecell.offset(,-1) = ""
try
c.offset(0,-1) = ""

regards
FSt1

 

[JLGWhiz]

Fundamentally, it looks ok, but try this.

Dim lr As Long, c As Range

lr = Sheets("PivotSheet").Cells(Rows.Count, 3).End(xlUp).Row

For Each c In Sheets("PivotSheet").Range("C4:C" & lr)
If c.Value = "" And c.Offset(0, -1).Value <> "" Then
c.Offset(, -1) = ""
End If
Next c

 

[Patrick Molloy]

not "active cell", just use C

Dim lr As Long
Dim C as Range
lr = Sheets("PivotSheet").Cells(Rows.Count, 1).End(xlUp).Row

For Each C In Sheets("PivotSheet").Range("C4:C" & lr)
If C.Value = "" Then
C.Offset(, -1) = ""
End If
Next C

 

[ryguy7272]

Ah! Yes! Makes sense.
Thanks everyone!!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

not "active cell", just use C

Dim lr As Long
Dim C as Range
lr = Sheets("PivotSheet").Cells(Rows.Count, 1).End(xlUp).Row

For Each C In Sheets("PivotSheet").Range("C4:C" & lr)
If C.Value = "" Then
C.Offset(, -1) = ""
End If
Next C

 

[Rick Rothstein]

If you are still reading this thread, I think this macro will do what you want...

Sub ClearBCellsForBlankCCells()
On Error Resume Next
Columns("C").SpecialCells(xlCellTypeBlanks).Offset(, -1).Value = ""
End Sub

 

[ryguy7272]

Wow! You can do that with just two lines of code!! this is definitely going
to be filed away in my library of VBA code.
Thanks Rick!! Thanks everyone!!

Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

If you are still reading this thread, I think this macro will do what you want...

Sub ClearBCellsForBlankCCells()
On Error Resume Next
Columns("C").SpecialCells(xlCellTypeBlanks).Offset(, -1).Value = ""
End Sub