Clearing content for only specific columns with VBA.

sudhansu121

New Member
Joined
Jan 19, 2021
Messages
28
Office Version
  1. 365
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?

VBA Code:
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
 ws.Range("B5:H14,L5:KW14").ClearContents
 

sudhansu121

New Member
Joined
Jan 19, 2021
Messages
28
Office Version
  1. 365
Hi & welcome to MrExcel.
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
I'm sorry but I don't understand.
Will the columns to be cleared always be the same?
 

sudhansu121

New Member
Joined
Jan 19, 2021
Messages
28
Office Version
  1. 365

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
In that case you can do that like
VBA Code:
 ws.Range("B5:H14,L5:R14,V5:AB14,AF5:AL14").ClearContents
 

sudhansu121

New Member
Joined
Jan 19, 2021
Messages
28
Office Version
  1. 365

ADVERTISEMENT

In that case you can do that like
VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,140
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe...
VBA Code:
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
 
Solution

sudhansu121

New Member
Joined
Jan 19, 2021
Messages
28
Office Version
  1. 365
A
Maybe...
VBA Code:
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

New Member
Joined
Jan 19, 2021
Messages
28
Office Version
  1. 365
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.
 

Forum statistics

Threads
1,136,303
Messages
5,674,966
Members
419,537
Latest member
ucatchy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top