How do i generate a value in every 2000 rows in column A?

[Waboku]

Hello,

for something I am doing for my work it would be very efficient to be able to use control down to the next 2000th cell instead of scrolling down to it. If anyone knows how to generate a value in those cells please consider replying, I would really appreciate the help.

I would like to generate an "x" or a "0" for example every 2000 cells. So starting with Cell 1 in column A, then Cell 2001, then cell 4001 and so on for the entire column.

Thanks

 

[jeffreybrown]

Something like this...

Sub Insert_Every_2000()
    Dim i As Long
    For i = 1 To 50000 Step 2000 'Change to suit
        Range("A" & i).Value = "X"
    Next i
End Sub

 

[Waboku]

Thanks Jeff. That worked. I'm sure it will save me a few days. It was also nice to use vba for the first time.

 

[Rick Rothstein]

Thanks Jeff. That worked. I'm sure it will save me a few days. It was also nice to use vba for the first time.

Here is another macro that you can consider for future use...

[table="width: 500"]
[tr]
	[td]Sub Xevery2000rows()
  Range("A1:A" & Rows.Count) = Evaluate("IF(MOD(ROW(A1:A" & Rows.Count & "),2000)=1,""X"","""")")
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

Here is another macro that you can consider for future use...

[table="width: 500"]
[tr]
	[td]Sub Xevery2000rows()
  Range("A1:A" & Rows.Count) = Evaluate("IF(MOD(ROW(A1:A" & Rows.Count & "),2000)=1,""X"","""")")
End Sub[/td]
[/tr]
[/table]

Actually, given the fixed range being covered by this macro, the code cam be compacted somewhat like this...

Sub Xevery2000rows()
  [A1:A1048576] = [IF(MOD(ROW(A1:A1048576),2000)=1,"Z","")]
End Sub