Can't count past 256!

JohnPoole

Active Member
Joined
Jun 9, 2005
Messages
267
Hi all, I seem to be having a problem getting the following code to place the numbers in columd D after row 256, wherby the it goesback to cell D2 and places the remaning numbers in that cell. I cant understand why, can anyone help?

Here is the code im using.

<font face=Courier New>


<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> count256()
Counter = 0
        <SPAN style="color:#00007F">Do</SPAN>
    
        Selection = Cells(Columns.Count, 4).End(xlUp).Offset(1, 0).Activate
        Selection.Value = Counter
        Counter = Counter + 1
        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> Counter = 300
    
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

As far as i can tell this should put the numgbers 0-300 into column D.

Many thanks.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I may be wrong, but:
It appears that the limitation might be from using the Columns.Count

Should this instead be a Rows.Count?

Selection = Cells(Rows.Count,4).End(xlUp).Offset(1,0).Activate
 
Upvote 0
There are 256 columns on a spreadsheet.

Your Cells(256,4).end(xlup)..... begins at row 256, so once a value is in that cell, the xlend takes it up to the top of the data.

You probably want 'rows.count'. :)
 
Upvote 0
Does this do what you want?

Code:
Sub test()
Dim r As Range
Set r = Range("D65536").End(xlUp).Offset(1)
r = 1
r.Resize(300, 1).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=300
End Sub
 
Upvote 0
Just wondering, but since you didn't have a dim statement for counter, is it possible it defined itself as "byte", which has a max limit of 256? I wonder if declaring Counter as Integer might solve your problem as well.
 
Upvote 0
PolarBear

If you don't declare a variable, or don't specify the data type when you do it is a Variant.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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