Avatar of Seamus2626
Seamus2626
Flag for Ireland 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
Microsoft Excel

Avatar of undefined
Last Comment
m4trix

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
m4trix

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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!