<div>
Try this workbook. Double-click cell A57 or any other cell in column &quot;A&quot;. The code assumes that the first data row is row 5.<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154634/29013873.xlsm" target="_blank" class="file-inline" title="35 up and down (17 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>29013873.xlsm</a>
</div>


29013873.xlsm

<div>
Hi Lee Thanks for you Answer!<br />
<br />
but filtering wont &nbsp;work because i need to select the 35 rows up and down from the number
</div>


<div>
I realized that; then couldn't edit my response after Martin's comment.<br />
Once the number is identified... you want to select the range of 35 records before and 35 records after the specific number.
</div>


<div>
Yes Lee <br />
but the number in data column can be 12 times <br />
so i need that secuence <br />
12 times 35 rows up and 35 rows downs as the sequence
</div>


<div>
<blockquote>
but the numeber in data column can be 12 times
</blockquote>
Please explain what that means. Are you selecting a <b>range</b> of cells in column &quot;A&quot;?
</div>


<div>
Sounds like the 35 range count for each iteration of the specific number.<br />
If 985 is the target number and it appears 12 times, there will be 12 ranges for each 985.
</div>


<div>
Martin <br />
<br />
the data column have over 3000 recods <br />
when i need an number from there &nbsp;can be 12 or 15 times &nbsp;in the column of data <br />
that number repeat &nbsp;everytime is repeat i need take the 35 rows up and 35 down from the number<a href="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154651/35-rows-up.png" target="_blank" title="select data (36 KB)"><img alt="select data" class="file-block lazyload" style="max-width: 458px;" data-src="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154651/35-rows-up.png" /></a><br />
then i need copy the numbers from that column <br />
as the sequence is <br />
<br />
lets see as the sample
</div>


35-rows-up.png

<div>
Did you try the workbook I posted? Double-click 985 (A57) &nbsp;and then double-click 848 (A52). Is that what you want?
</div>


<div>
Martin i did <br />
several times but nothing happens!!
</div>


<div>
My code was copying to the wrong column. <b>Double-click</b> a cell in column &quot;A&quot; in this workbook.<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154667/29013873a.xlsm" target="_blank" class="file-inline" title="aaaaaa (17 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>29013873a.xlsm</a>
</div>


29013873a.xlsm

<div>
Martin i have no test yet <br />
but here an sample with number 468 is repeat 11 times &nbsp;then i need 35 rows &nbsp;an downs for each time it appear on<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154671/468-test.png" target="_blank" title="test (26 KB)"><img alt="test" class="file-block lazyload" style="max-width: 409px;" data-src="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154671/468-test.png" /></a>
</div>


468-test.png

<div>
MArtin is working!!<br />
but <br />
if possible to take them to a new sheet <br />
and takes all the times it repeat in the data <br />
for example 468 appear 11 times &nbsp;i need all the times is in
</div>


<div>
In this workbook the number 123 appears four times. Please update Sheet2 to show what you want the output to look like and re-attach the updated workbook.<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154689/29013873b.xlsm" target="_blank" class="file-inline" title="Please update (21 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>29013873b.xlsm</a>
</div>


29013873b.xlsm

<div>
Martin here s the data &nbsp;on work<br />
have like 3000 records but will be growing every day for teh years to comes<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154692/DATA-ON.xlsx" target="_blank" class="file-inline" title="DATA (31 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>DATA-ON.xlsx</a>
</div>


DATA-ON.xlsx

<div>
In order for me to be able to help you I need you to do what I asked in ID: 42078609 and that is to update sheet2 in <u>that</u> workbook to show what it would look like if you selected one of the &quot;123&quot; cells? By the way, would the output look different if you selected row 12 as compared with row 43?
</div>


<div>
Martin ! i open the file you post<br />
hit 123 but nothings happens <br />
but i recreate in sheet 2(2) <br />
what the out should be<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154701/29013873b--1-.xlsm" target="_blank" class="file-inline" title="35ROWS UP AND DOWN (21 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>29013873b--1-.xlsm</a>
</div>


29013873b--1-.xlsm

<div>
Martin the output should be always 35 rows and 35 rows down from the numever selected<br />
in the case nothing there should be empty<br />
<br />
here i put an sample &nbsp;with the all data column<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154713/DATA-ON.xlsx" target="_blank" class="file-inline" title="test xample (38 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>DATA-ON.xlsx</a>
</div>


<div>
In workbook 29013873b--1-.xlsm, why does the output in column &quot;I&quot; start in row 14?
</div>


<div>
Good Point!! <br />
<br />
we look for number 123<br />
&nbsp;it not start in column row 14 &nbsp;<br />
<br />
&nbsp;it start in row 48 <br />
is where the number 123 is located<br />
<br />
but &nbsp;after 123 is identify <br />
need 35 rows up and 35 rows down <br />
<br />
then column I row 14 show &nbsp;as you see there<br />
the others don't begin in row 14 because first is empty the other &nbsp;because are not with the complete 35 rows needed<br />
so you rigth !!<br />
<br />
&nbsp;<a href="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154756/123-position.png" target="_blank" title="123-position.png (37 KB)"><img alt="123-position.png" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2017/04_w14/800_1154756/123-position.png" /></a>
</div>


123-position.png

<div>
in this sample is 4 times because data is short just to show you<br />
but with the data can be more than &nbsp;times like 18 times or more
</div>


<div>
I know that there can be more than 4 times.<br />
<br />
In this case the last occurrence of &quot;123&quot; is in row 43, not row 48. Are you saying that the calculation for the first row of the last column on Sheet2 (in this case column &quot;I&quot;) should always be the row of the last occurrence - 35 + 5, which in this case is 43 - 35 + 5 = 14?
</div>


<div>
Martin &nbsp;im not so good like you with equations you a master <br />
<br />
but <br />
<br />
what i did was copy all the instances of 123 in the same row (sheet 2) so is like an central point &nbsp;that why is in yellow <br />
from that point ((the Central point )) the 35 rows up and 35 downs that come from column A when identify it
</div>


<div>
When I said &quot;the last occurrence of &quot;123&quot; is in row 43&quot; I was wrong, it's 138. Anyhow I'll figure it out.
</div>


<div>
Try this. Sheet2 shows the result of double-clicking any of the &quot;123&quot; cells on Sheet1.<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154779/29013873c.xlsm" target="_blank" class="file-inline" title="ccccc (27 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>29013873c.xlsm</a>
</div>


29013873c.xlsm

<div>
wAOOOOOO!! GREAT JOB !!MARTIN !!<br />
YOU !!THE BEST OF THE BEST !!!
</div>


<div>
Please don't forget to close this question.
</div>


<div>
i was testing <br />
Martin &nbsp;have some issues<br />
i try some numbers and &nbsp;don't get all <br />
for example i have 17 using the find tool<br />
and it get 15 <br />
<br />
could i use all the column a to put the data ??
</div>


<div>
should be a minor adjustment
</div>


<div>
Attach the workbook where you have the 17 number and tell me what that number is.
</div>


<div>
Yes!!<br />
when you look the data list with the find and replace tool you get one number <br />
and the macro give other<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154790/35-UP-AND-DOWN.xlsm" target="_blank" class="file-inline" title="35updown (92 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>35-UP-AND-DOWN.xlsm</a>
</div>


35-UP-AND-DOWN.xlsm

29013873d.xlsm

<div>
Martin ...I appreciate ! so much !! &nbsp;you help &nbsp;and patience with me ...<br />
thanks for &nbsp;listened to me <br />
and be an great friend!!<br />
A great Hug and affectionate kiss…
</div>


<div>
<div class="content wysiwyg-content">
I have an column with Data ! over 3000 records !<br />
i have a number i need to be identify<br />
<br />
when identify i need go to the number and <br />
select 35 rows and &nbsp;and 35 downs from the data column<br />
<br />
let's say by this sample<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w14/1154626/need-35-rows-up-and-dows.xlsx" target="_blank" class="file-inline" title="select data (11 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>need-35-rows-up-and-dows.xlsx</a>
</div>
</div>


need-35-rows-up-and-dows.xlsx

I have an column with Data ! over 3000 records !
i have a number i need to be identify

when identify i need go to the number and 
select 35 rows and  and 35 downs from the data column

let's say by this sample

How can i select rows up and down an specific number !

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.