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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,189
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,141
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

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

Attachments

  • Capture_VBA.PNG
    Capture_VBA.PNG
    46.8 KB · Views: 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,189
Office Version
  1. 365
Platform
  1. Windows
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

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

ADVERTISEMENT

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.
 

Attachments

  • Capture_VBA.PNG
    Capture_VBA.PNG
    46.8 KB · Views: 2

sudhansu121

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,141
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

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

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,141
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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"
 

Forum statistics

Threads
1,136,864
Messages
5,678,216
Members
419,752
Latest member
TryingtoLearnVBA

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