If blank, offset and clear contents

Seamus2626
Seamus2626 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Try something like this:

Sub Macro1()
    Dim lastRow As Integer, i As Integer
    
    lastRow = Range("A65569").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


Commented:
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
Most Valuable Expert 2011
Awarded 2010
Commented:
With Excel 2007 and later, there are more than 65569 rows, so you may want to use in row 4 of the code:

    lastRow = cells(rows.count,"A").end(xlup).Row

This will work in Excel 2003 and earlier, and in all later versions, because it will take into consideration all rows in the sheet, no matter how many there are.

cheers, teylyn

OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Author

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

Method'Range' of object' _Global'failed

Seamus

Author

Commented:
Thanks guys!
Most Valuable Expert 2011
Awarded 2010

Commented:
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

Commented:
Thanks teylyn. I was trying to remember that bit!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial