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]

Great Fluff this is working perfect.

It was Mark that supplied the code not me.

What you are saying now makes no sense you want to clear 11-17 but then say not to touch 12?

 

[MARK858]

Great Fluff this is working perfect.

Are you responding to me or Fluff?

If me then I do not know how to do the below without setting individual ranges as it appears to follow no set pattern.

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.

 

[sudhansu121]

It was Mark that supplied the code not me.

What you are saying now makes no sense you want to clear 11-17 but then say not to touch 12?

Sorry Fluff.
But this is how the format is set for the template.
Basically from column 1 to 10
We need to clear the data from 1, (2 contains formula so do not want to remove it),3,4,5,6,7,8 (9 and 10 should be left as it is)
I have attached a screenshot

 

[Fluff]

Virtually every post you have made changes or contradicts what you said before.
On that basis I suggest you manually set the ranges as I showed.

 

[sudhansu121]

Are you responding to me or Fluff?

If me then I do not know how to do the below without setting individual ranges as it appears to follow no set pattern.

Sorry Mark, I was replying to you.
Thanks a lot for your support
Please see the screenshot.

your code is working brilliantly. I just realized that column C data should also not be cleared off. I am so sorry for the misunderstanding.

 

[sudhansu121]

Virtually every post you have made changes or contradicts what you said before.
On that basis I suggest you manually set the ranges as I showed.

Thanks a lot Fluff and I appreciate your help .
Have a nice day ahead and please accept my apologies for the misunderstanding.

 

[MARK858]

First of all rather can you please use the boards XL2BB addin rather than post images.

Second, can you please list up to column 50 all the columns that you don't want cleared please.

 

[sudhansu121]

First of all rather can you please use the boards XL2BB addin rather than post images.

Second, can you please list up to column 50 all the columns that you don't want cleared please.

I just modified the code and it is working fine.

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

Dim i As Long

For i = 2 To Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column Step 3

Intersect(Columns(i).Resize(, 1), Rows("5:14")).ClearContents

Next

For i = 4 To Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column Step 8

Intersect(Columns(i).Resize(, 6), Rows("5:14")).ClearContents

Next

End Select

Next ws

End Sub

 

[MARK858]

Are you sure that it is working fine? there are 6 cleared columns after column C but 7 cleared columns after columns S, AA etc and R & S both are uncleared.

Also the columns will only be cleared on the activesheet rather than every sheet bar "DATOS" and "RESUMEN DÍA"