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

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

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---
 
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
 
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
 
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
 
Ah! Yes! Makes sense.
Thanks everyone!!
Ryan--

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


Patrick Molloy said:
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
Click to expand...
 
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
 
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''.


Rick Rothstein said:
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
Click to expand...
 
Back
Top