Clearing content for only specific columns with VBA.

[sudhansu121]

Hi Experts,
I have a sheet with a long range (B5:KW14) and each time I have to clear the data manually.
So I have to clear content from B5:H14 and the leave 2 column and aging Start clearing data from L5:L14 so on up to KP5:KW14.
The twist here is after B5:H14, I do not want to clean Column I,J,K and then again start cleaning from L5:L14. I am actually stuck here. I have prepared a macro that clean from B5:KW14.

So may I kindly ask you to help me here?

Sub CleardatafirstPart()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
 Case "DATOS", "RESUMEN DÍA"
 ' do nothing
 Case Else
 ws.Range("B5:KW14").ClearContents
 ws.Range("B24:KW33").ClearContents
 End Select
Next ws
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

 ws.Range("B5:H14,L5:KW14").ClearContents

 

[sudhansu121]

Hi & welcome to MrExcel.
How about

ws.Range("B5:H14,L5:KW14").ClearContents

Hi Fluff,
Thanks for supporting me.
The problem is I have to do this manually then. After each 7 columns I have to leave 3 columns untouched.
now with your code it will clear fromB5 to H14 and leave ,I,J,K untouched. But if we use the code L5:KW15, then it will clear the data from S,T,U column and so on.

So I want something so that It can automatically take 7 column and clear data and then leave 3 column untouched until last column

 

[Fluff]

I'm sorry but I don't understand.
Will the columns to be cleared always be the same?

 

[sudhansu121]

I'm sorry but I don't understand.
Will the columns to be cleared always be the same?

Yes the columns to be cleared will always be same.
To elaborate,
For example I have 20 columns in Excel.
I want to clear data for each 7 columns and then next 3 coulmns would remain same and data should not be cleaned.

So column 1, to 7- Clear Content
8,9 and 10 No changes needed.
Then again Column 11 to 17 -clear content
18,19 and 20 No changes needed.

So for my excel File the logic is same as above it just should be automated and consider up to last column

 

[Fluff]

In that case you can do that like

 ws.Range("B5:H14,L5:R14,V5:AB14,AF5:AL14").ClearContents

 

[sudhansu121]

In that case you can do that like

ws.Range("B5:H14,L5:R14,V5:AB14,AF5:AL14").ClearContents

Yes Fluff this is exactly what I want Is there a way to do it Automatically? So I do not have to write the whole thing ?

 

[MARK858]

Maybe...

Sub sudhansu121()
    Dim i As Long
    For i = 2 To Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column Step 10
        Intersect(Columns(i).Resize(, 7), Rows("5:14")).ClearContents
    Next
End Sub

 

[sudhansu121]

A

Maybe...

Sub sudhansu121()
    Dim i As Long
    For i = 2 To Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column Step 10
        Intersect(Columns(i).Resize(, 7), Rows("5:14")).ClearContents
    Next
End Sub

Allow me 30 Mins and will update you

 

[sudhansu121]

A

Allow me 30 Mins and will update you

Great Fluff this is working perfect.
I just found out one more problem and we need t modify the code.

After the 1st column we should not change the data at column 2,12 and so on.

I want to clear data for each 7 columns and then next 3 columns would remain same and data should not be cleaned.

So column 1, to 7- Clear Content
2,8,9 and 10 No changes needed.
Then again Column 11 to 17 -clear content
12,18,19 and 20 No changes needed.