Searching Columns for blank cells and editing

davidkoh79 · #1 (**permalink**) 05-04-04, 03:44

Hi there

I need help urgently.I have 6 columns(eg: Column O,Q,S,U,W,Y).And they are according to priority where Column O is the highest priority and Column Y is the least priority.Column N,P,R,T,V,X will contain some data.

I need to write a VB code that will scan through Column O(the highest priority column) for a blank cell.And once it finds a blank cell,it will make the cells in Column N,P,Q,R,S,T,U,V,W,X,Y (of the same row) blank also.I can't delete the whole row or make the whole row blank as I need to use the data in the Columns A to M.I need to repeat this for the whole of Column O.

After doing Column O,I need to scan through the second highest priority column(eg: Column Q) ) for a blank cell.And once it finds a blank cell,it will make the cells in Column P,R,S,T,U,V,W,X,Y (of the same row) blank also.And I need to repeat this for the whole of Column Q also.

I need to do the same for the remaining columns(Column S,U,W,Y).I was thinking of using a If-Then-Else statement but I don't know how to start.

Pls help.Thanks

David

DavidCoutts · #2 (**permalink**) 05-04-04, 08:14

Hi David

Ive put some code together for you.

Code:

Sub Test()
    Dim Lastrow As Double
    Dim I As Integer, J As Integer
    
    'Gets Lastrow of Data  assuming that column A is Completely Filled
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row
    
    'Clear the Corresponding Data(O to Y every Second Column)
    For I = 15 To 25 Step 2
        With Range(Cells(2, I), Cells(Lastrow, I)).SpecialCells(xlCellTypeBlanks)
           .Offset(0, -1).Clear
            For J = 1 To (11 - I + 15)  'Counts the Columns to Y
                .Offset(0, J).Clear
            Next J
        End With
    Next I
End Sub

HTH

David

davidkoh79 · #3 (**permalink**) 05-04-04, 22:41

Hi David

Thanks a lot for your help.The code works perfectly.U are a life saver! Thanks again.

David

Internet Services

Web Development

Digital Marketing

Consumer Tech