# Thread: Searching Columns for blank cells and editing

1. Registered User
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. Registered User
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. Registered User
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
•