Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    21

    Unanswered: Searching Columns for blank cells and editing

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  3. #3
    Join Date
    Feb 2004
    Posts
    21
    Hi David

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

    David

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •