asked on

If blank, offset and clear contents

Hi,

Im looking for a litte sub that can count ColumnA and use that as its range then, loop trough Column I and if the cell is blank, clear the contents of the corresponding cell in J, so if I4 was blank, clear K4.

I would only want this to loop as far as the count of ColumnA

Thanks
Seamus

ASKER CERTIFIED SOLUTION

m4trix

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

m4trix

Note that in your description you said corresponding cell in J, then said K4... so I'm not sure if it's J or K you want done. I went with K, but if it should be J, just change the "K" to "J" in line 7

SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Seamus2626

ASKER

Thanks M4trix, but its giving me an error on line 4 saying

Method'Range' of object' _Global'failed

Seamus

Seamus2626

ASKER

Thanks guys!

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Works fine for me with this code

Sub Macro1()
    Dim lastRow As Integer, i As Integer
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To lastRow
    
        If Range("I" & i) = "" Then Range("K" & i).ClearContents
    Next i
End Sub

Open in new window

see attached

cheers, teylyn
Book2.xlsm

m4trix

Thanks teylyn. I was trying to remember that bit!