Thread: Compare two columns and delete cell in one column if cell next to it is blank

Hello all,

I need a macro which will look at two columns, for example B and C, and if there isn't a value in the cell in Column C, clear (or change the value to zero if it's easier) the adjacent cell in column B (i.e no value in C15 would clear B15). The macro would need to do this all the way down columns B and C, and then move on to columns D and E, and do the same thing, because my data is grouped in columns of two, and I have too many columns to filter them all manually.

I hope that someone can help me with this, I had rarely used Excel before starting my dissertation and i've been thrown in the deep end with it.

Thank you very much in advance,

Jacob

Hi jacobhandson

possibly something like...

Sub ptest()
Dim xCell As Range
myarray = VBA.Array("C", "3", "E", "5")
For xKey = 1 To 3 Step 2
For Each xCell In Range(myarry(xKey) & "1", Cells(Rows.Count, myarray(xKey + 1)).End(xlUp))
  If IsEmpty(xCell.Value) Then xCell.Offset(0, -1).Value = ""
Next xCell
End Sub

There was a small line missing. Added it in here -

Sub ptest()
Dim xCell As Range
myarray = VBA.Array("C", "3", "E", "5")
For xKey = 1 To 3 Step 2
For Each xCell In Range(myarry(xKey) & "1", Cells(Rows.Count, myarray(xKey + 1)).End(xlUp))
  If IsEmpty(xCell.Value) Then xCell.Offset(0, -1).Value = ""
Next xCell
Next xKey
End Sub

There is a typo in this line -

 For Each xCell In Range(myarry(xKey) & "1", Cells(Rows.Count, myarray(xKey + 1)).End(xlUp))

Change it to

 For Each xCell In Range(myarray(xKey) & "1", Cells(Rows.Count, myarray(xKey + 1)).End(xlUp))

I guess it would be better if Pike assisted you himself, since i tried figuring it out but couldn't. You could PM him to expedite a response.

Hi jacobhandson

Ive test this code , the other i just typed out

Option Explicit
Sub ptest()
    Dim xCell As Range
    Dim myarray
    Dim xkey As Integer
    myarray = VBA.Array("C", 3, "E", 5)
    For xkey = 0 To 3 Step 2
        For Each xCell In Range(myarray(xkey) & "1", Cells(Rows.Count, myarray(xkey + 1)).End(xlUp))
            If IsEmpty(xCell.Value) Then xCell.Offset(0, -1).Value = ""
        Next xCell
    Next xkey
End Sub

Perhaps

Sub test()
    Dim oneColumn As Range
    For Each oneColumn In Sheet1.Range("C1,E1").Columns
        With oneColumn.EntireColumn
            On Error Resume Next
            Application.Intersect(.Offset(0, -1), .SpecialCells(xlCellTypeBlanks).EntireRow).ClearContents
            On Error GoTo 0
        End With
    Next oneColumn
End Sub

Thanks, the first one (post #9) does what I want for columns B to E (but only to about row 178), is there any chance that you could tell me how to modify this to make it do the same thing all the way up to column YI please, and down to row 366? I assume it's meant to do the latter but it isnt?!

post #10 doesnt seem to work for me i'm afraid. thanks for the attempt nontheless

Are the blank cells truely blank or do they have formulas that evaluate to "" ?
(eg =IF(A1=1, "cat", "") )

Can anyone help me to change the range of the code from post #9: I need it to iteratively perform the function over the full range of the data without me having to change "C" and "E" to other letters hundreds of times.

I've been told you can use currentregion property to find the range size without having to us end(xlup).
and that you can work on each column by using columns.count property eg
for each col in currentregion.columns
for each row in currentregion,rows

but again, i have no clue where to start to do this.

any help would be greatly appreciated,

Thanks,

Jacob