# Limit the Number of rows when dragging a formular

#### Qamarjulius

##### New Member
Hi Mr excel,
Is a way i can limit my rows in column A such that everytime i double click to drag it only goes up to 05 at a time.
 A B H100 Jan-11 H101 Jan-11 H102 Jan-11 H103 Jan-11 H104 Jan-11 H105 Jan-11 H200 Feb-11 H201 Feb-11 H202 Feb-11 H203 Feb-11 H204 Feb-11 H205 Feb-11 H300 Mar-11 H301 Mar-11 H302 Mar-11 H303 Mar-11 H304 Mar-11 H305 Mar-11 H400 Apr-11 H401 Apr-11 H402 Apr-11

<colgroup><col style="width: 48pt;" span="2" width="64"> <tbody>
</tbody>

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
This should work

="H"&INT((ROWS(\$1:1)-1)/6)+100+MOD((ROWS(\$1:1)-1),5)

UPDATE: But it doesn't Still working on this...

="H"&INT((ROWS(\$1:1)-1)/5)*100+100+MOD((ROWS(\$1:1)-1),5)

Rich (BB code):
``="H"&INT((ROWS(\$1:1)-1)/6)*100+100+MOD((ROWS(\$1:1)-1),6)``

Rich (BB code):
``="H"&INT((ROWS(\$1:1)-1)/6)*100+100+MOD((ROWS(\$1:1)-1),6)``

[wow!, this is great!]
[it works upto H905, after that i need it to replace 10 with O for October, then 11 with N for November and 12 with D for December]

[ see, the code H102 for instace, is a lot No, where H represents the year of manufacture, 1 represents Jan and 02 represents the second batch to be produced. The lot No cannot accomodate 6 digits(at least in this case) hence the need to replace 10,11 and 12 with one digit letters.]
[thanks for the good work so far]

Replies
0
Views
958
Replies
2
Views
839

1,219,959
Messages
6,151,153
Members
451,011
Latest member
Pigdog89

### 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.

### Which adblocker are you using?

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

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