Is Offset the best option for me

S

Stuart54

New Member
Joined
Apr 29, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I have a spreadsheet that I have to input different information into every week, so every week I have to go to a certain range of cells and clear all the data in those cells, So I have written a macro to find a word in my heading then go one cell down and clear it, but as it is a range of cells, I have the input below code, to cover the area to clear. Is there some way I can shorten this to be a range. I cannot use "Range" as the cells I have to clear every week are not in the same place.

Set FoundItem = Range("A:A").Find(",Name") - This is my heading name and it is in column A, my range is 12 rows down and 12 columns across
FoundItem.Offset(1, 0) = ""
FoundItem.Offset(2, 0) = ""
FoundItem.Offset(3, 0) = ""
FoundItem.Offset(4, 0) = ""
FoundItem.Offset(5, 0) = ""
FoundItem.Offset(6, 0) = ""
FoundItem.Offset(7, 0) = ""
FoundItem.Offset(8, 0) = ""
FoundItem.Offset(9, 0) = ""
FoundItem.Offset(10, 0) = ""
FoundItem.Offset(11, 0) = ""
FoundItem.Offset(12, 0) = ""
FoundItem.Offset(1, 1) = ""
FoundItem.Offset(2, 1) = ""
FoundItem.Offset(3, 1) = ""
FoundItem.Offset(4, 1) = ""
FoundItem.Offset(5, 1) = ""
FoundItem.Offset(6, 1) = ""
FoundItem.Offset(7, 1) = ""
FoundItem.Offset(8, 1) = ""
FoundItem.Offset(9, 1) = ""
FoundItem.Offset(10, 1) = ""
FoundItem.Offset(11, 1) = ""
FoundItem.Offset(12, 1) = ""
FoundItem.Offset(1, 2) = ""
FoundItem.Offset(2, 2) = ""
FoundItem.Offset(3, 2) = ""
FoundItem.Offset(4, 2) = ""
FoundItem.Offset(5, 2) = ""
FoundItem.Offset(6, 2) = ""
FoundItem.Offset(7, 2) = ""
FoundItem.Offset(8, 2) = ""
FoundItem.Offset(9, 2) = ""
FoundItem.Offset(10, 2) = ""
FoundItem.Offset(11, 2) = ""
FoundItem.Offset(12, 2) = ""
FoundItem.Offset(1, 3) = ""
FoundItem.Offset(2, 3) = ""
FoundItem.Offset(3, 3) = ""
FoundItem.Offset(4, 3) = ""
FoundItem.Offset(5, 3) = ""
FoundItem.Offset(6, 3) = ""
FoundItem.Offset(7, 3) = ""
FoundItem.Offset(8, 3) = ""
FoundItem.Offset(9, 3) = ""
FoundItem.Offset(10, 3) = ""
FoundItem.Offset(11, 3) = ""
FoundItem.Offset(12, 3) = ""
FoundItem.Offset(1, 4) = ""
FoundItem.Offset(2, 4) = ""
FoundItem.Offset(3, 4) = ""
FoundItem.Offset(4, 4) = ""
FoundItem.Offset(5, 4) = ""
FoundItem.Offset(6, 4) = ""
FoundItem.Offset(7, 4) = ""
FoundItem.Offset(8, 4) = ""
FoundItem.Offset(9, 4) = ""
FoundItem.Offset(10, 4) = ""
FoundItem.Offset(11, 4) = ""
FoundItem.Offset(12, 4) = ""
FoundItem.Offset(1, 5) = ""
FoundItem.Offset(2, 5) = ""
FoundItem.Offset(3, 5) = ""
FoundItem.Offset(4, 5) = ""
FoundItem.Offset(5, 5) = ""
FoundItem.Offset(6, 5) = ""
FoundItem.Offset(7, 5) = ""
FoundItem.Offset(8, 5) = ""
FoundItem.Offset(9, 5) = ""
FoundItem.Offset(10, 5) = ""
FoundItem.Offset(11, 5) = ""
FoundItem.Offset(12, 5) = ""
FoundItem.Offset(1, 6) = ""
FoundItem.Offset(2, 6) = ""
FoundItem.Offset(3, 6) = ""
FoundItem.Offset(4, 6) = ""
FoundItem.Offset(5, 6) = ""
FoundItem.Offset(6, 6) = ""
FoundItem.Offset(7, 6) = ""
FoundItem.Offset(8, 6) = ""
FoundItem.Offset(9, 6) = ""
FoundItem.Offset(10, 6) = ""
FoundItem.Offset(11, 6) = ""
FoundItem.Offset(12, 6) = ""
FoundItem.Offset(1, 7) = ""
FoundItem.Offset(2, 7) = ""
FoundItem.Offset(3, 7) = ""
FoundItem.Offset(4, 7) = ""
FoundItem.Offset(5, 7) = ""
FoundItem.Offset(6, 7) = ""
FoundItem.Offset(7, 7) = ""
FoundItem.Offset(8, 7) = ""
FoundItem.Offset(9, 7) = ""
FoundItem.Offset(10, 7) = ""
FoundItem.Offset(11, 7) = ""
FoundItem.Offset(12, 7) = ""
FoundItem.Offset(1, 8) = ""
FoundItem.Offset(2, 8) = ""
FoundItem.Offset(3, 8) = ""
FoundItem.Offset(4, 8) = ""
FoundItem.Offset(5, 8) = ""
FoundItem.Offset(6, 8) = ""
FoundItem.Offset(7, 8) = ""
FoundItem.Offset(8, 8) = ""
FoundItem.Offset(9, 8) = ""
FoundItem.Offset(10, 8) = ""
FoundItem.Offset(11, 8) = ""
FoundItem.Offset(12, 8) = ""
FoundItem.Offset(1, 9) = ""
FoundItem.Offset(2, 9) = ""
FoundItem.Offset(3, 9) = ""
FoundItem.Offset(4, 9) = ""
FoundItem.Offset(5, 9) = ""
FoundItem.Offset(6, 9) = ""
FoundItem.Offset(7, 9) = ""
FoundItem.Offset(8, 9) = ""
FoundItem.Offset(9, 9) = ""
FoundItem.Offset(10, 9) = ""
FoundItem.Offset(11, 9) = ""
FoundItem.Offset(12, 9) = ""
FoundItem.Offset(1, 10) = ""
FoundItem.Offset(2, 10) = ""
FoundItem.Offset(3, 10) = ""
FoundItem.Offset(4, 10) = ""
FoundItem.Offset(5, 10) = ""
FoundItem.Offset(6, 10) = ""
FoundItem.Offset(7, 10) = ""
FoundItem.Offset(8, 10) = ""
FoundItem.Offset(9, 10) = ""
FoundItem.Offset(10, 10) = ""
FoundItem.Offset(11, 10) = ""
FoundItem.Offset(12, 10) = ""
FoundItem.Offset(1, 11) = ""
FoundItem.Offset(2, 11) = ""
FoundItem.Offset(3, 11) = ""
FoundItem.Offset(4, 11) = ""
FoundItem.Offset(5, 11) = ""
FoundItem.Offset(6, 11) = ""
FoundItem.Offset(7, 11) = ""
FoundItem.Offset(8, 11) = ""
FoundItem.Offset(9, 11) = ""
FoundItem.Offset(10, 11) = ""
FoundItem.Offset(11, 11) = ""
FoundItem.Offset(12, 11) = ""
 

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".
Sort by date Sort by votes
OMG...
Replace all those FoundItem.Offset with:
VBA Code: 
FoundItem.Offset(1, 0).Resize(12, 12).ClearContents
Bye
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Help with calculation between work days based on condition
Replies
7
Views
267
Fluff
Fluff
P
  • Question
vba at each change
2
Replies
15
Views
693
kevin9999
K
J
  • Solved
IFS
Replies
4
Views
118
Peter_SSs
Peter_SSs
S
  • Solved
VBA Code Help
Replies
4
Views
289
Peter_SSs
Peter_SSs
I
  • Question
Formula to count duplicate Values every x # of rows
Replies
8
Views
277
Fluff
Fluff

Forum statistics

Threads
1,215,219
Messages
6,123,680
Members
449,116
Latest member
HypnoFant

Share this page

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
Back
Top