# need vba code like this to start and stop at 60,000 stop at 120,000 then start at 120,001 stop at 180,000 then start at 180,001 and stop at 240,000



## alan myers (Dec 6, 2022)

'*************************************
'creat hyper link  wit stop and starts
'*************************************
Sub CreateHyperLinks()
Dim sh As Worksheet
Dim rng As Range, c As Range
Set sh = ActiveSheet
With sh
Set rng = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
For Each c In rng.Cells
.Hyperlinks.Add Anchor:=c, Address:=c.Value, TextToDisplay:=c.Value
Next
End With
End Sub


thanks for aby help


----------



## Flashbond (Dec 6, 2022)

Lİke tihs?

```
Sub CreateHyperLinks()
  Dim sh As Worksheet
  Dim lRow As Integer
  Set sh = ActiveSheet
  With sh
    lRow = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = 60000 to 120000
      .Hyperlinks.Add Anchor:=.Cells(i, 1), Address:=.Cells(i, 1).Value, TextToDisplay:=.Cells(i, 1).Value
    Next
    For i = 120001 to 180000
      .Hyperlinks.Add Anchor:=.Cells(i, 1), Address:=.Cells(i, 1).Value, TextToDisplay:=.Cells(i, 1).Value
    Next
    For i = 180001 to 240000
      .Hyperlinks.Add Anchor:=.Cells(i, 1), Address:=.Cells(i, 1).Value, TextToDisplay:=.Cells(i, 1).Value
    Next
  End With
End Sub
```


----------



## HaHoBe (Dec 6, 2022)

Hi alan myers,

how about


```
Public Sub MrE_1223923_161540C()
' https://www.mrexcel.com/board/threads/need-vba-code-like-this-to-start-and-stop-at-60-000-stop-at-120-000-then-start-at-120-001-stop-at-180-000-then-start-at-180-001-and-stop-at-240-000.1223923/
' Created: 20221206
' By:      HaHoBe

  Dim lngLastRow As Long
  Dim lngCounter As Long
  Dim lngIntern As Long
 
  Const clngRowsPart As Long = 60000
  With ActiveSheet
    lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    For lngCounter = 1 To lngLastRow Step clngRowsPart
      For lngIntern = 1 To clngRowsPart
        If lngIntern + lngCounter - 1 > lngLastRow Then Exit For
        With .Cells(lngIntern + lngCounter - 1, 1)
          .Hyperlinks.Add Anchor:=wks.Cells(lngIntern + lngCounter - 1, 1), Address:=.Value, TextToDisplay:=.Value
        End With
      Next lngIntern
    Next lngCounter
  End With
End Sub
```

@ Flashbond


```
Dim lRow As Integer
```
Integer variables can hold values between -32,768 and 32,767. Long should be the better type. And i is not dimmed...

Ciao,
Holger


----------



## HaHoBe (Dec 6, 2022)

Hi alan myers,

a correction to the code (theoretical it should act like this):


```
Public Sub MrE_1223923_161540C_corr()
' https://www.mrexcel.com/board/threads/need-vba-code-like-this-to-start-and-stop-at-60-000-stop-at-120-000-then-start-at-120-001-stop-at-180-000-then-start-at-180-001-and-stop-at-240-000.1223923/
' Created: 20221206
' By:      HaHoBe

  Dim lngLastRow As Long
  Dim lngCounter As Long
  Dim lngIntern As Long
 
  Const clngRowsPart As Long = 60000
  With ActiveSheet
    lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    For lngCounter = 1 To lngLastRow Step clngRowsPart
      For lngIntern = 1 To clngRowsPart
        If lngIntern + lngCounter - 1 > lngLastRow Then Exit For
        With .Cells(lngIntern + lngCounter - 1, 1)
          .Hyperlinks.Add Anchor:=ActiveSheet.Cells(lngIntern + lngCounter - 1, 1), Address:=.Value, TextToDisplay:=.Value
        End With
      Next lngIntern
    Next lngCounter
  End With
End Sub
```

But even splitting the areas up will not enhance the number of hyperlinks on one worksheet: 65530.


Holger


----------



## Fluff (Dec 6, 2022)

@alan myers why have you started another thread on this, when you have already been told that it is not possible to have that number of hyperlinks?


----------



## HaHoBe (Dec 6, 2022)

Hi alan myers,

if the contents in Column A are either the complete addresses of websites or fully qualified paths and names of files (my system pops up a safety note although the path is added to the trusted locations) you may try using this event behind the sheet (right click on a cell in Column A to trigger) as this will work without hyperlinks:


```
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, ActiveSheet.UsedRange.Columns(1)) Is Nothing Then
  If Len(Trim(Target.Value)) > 0 Then
    ActiveWorkbook.FollowHyperlink (Target.Value), NewWindow:=True
    Cancel = True
  End If
End If
End Sub
```

Holger


----------



## alan myers (Dec 6, 2022)

Fluff said:


> @alan myers why have you started another thread on this, when you have already been told that it is not possible to have that number of hyperlinks?


you are a pain they told me about the limit I am not talking about I am looking for a way around this like I said read part of the data and do the changes then start at another cell  and then again not even talking about the first post just trying to get around the issue i thought this was a place for help you make it like pulling teeth i am follow rules


----------



## Fluff (Dec 6, 2022)

In future I would suggest that you abide by the rules (see rule#1).


alan myers said:


> I am looking for a way around this


But you cannot have that number of hyperlinks in a single sheet, end of full stop, regardless of what you want.
You can try what HaHoBe has suggested in post#6, but they will not be hyperlinks.


----------



## alan myers (Dec 6, 2022)

Fluff said:


> In future I would suggest that you abide by the rules (see rule#1).
> 
> But you cannot have that number of hyperlinks in a single sheet, end of full stop, regardless of what you want.
> You can try what HaHoBe has suggested in post#6, but they will not be hyperlso




I guess you don't know what you are talking about I have 85000 hyper links and it works


----------



## Fluff (Dec 6, 2022)

You are right I don't know what I'm on about as I have never tried to insert that number of Hyperlinks in an entire workbook, let alone a single sheet, I am simply going on what Microsoft state are the limits.
If you already have in excess of that, why did you not mention that in your previous post, so that members were aware that the information is wrong?


----------



## alan myers (Dec 6, 2022)

'*************************************
'creat hyper link  wit stop and starts
'*************************************
Sub CreateHyperLinks()
Dim sh As Worksheet
Dim rng As Range, c As Range
Set sh = ActiveSheet
With sh
Set rng = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
For Each c In rng.Cells
.Hyperlinks.Add Anchor:=c, Address:=c.Value, TextToDisplay:=c.Value
Next
End With
End Sub


thanks for aby help


----------



## alan myers (Dec 7, 2022)

Fluff said:


> You are right I don't know what I'm on about as I have never tried to insert that number of Hyperlinks in an entire workbook, let alone a single sheet, I am simply going on what Microsoft state are the limits.
> If you already have in excess of that, why did you not mention that in your previous post, so that members were aware that the information is wrong?


because I just found out have to do it.
 I am just tired of you busting my balls every time I get on here.
 and I am sorry  if I break a rule I do knot mean to.
 but in this case I don't see how I was breaking the rules the members told me the limit then was not talking about the limit I was looking for a way around it.
 so I don't see how I was breaking the rule and I did not start another post on the same subject .
I like getting on here and getting help.
and on my subject I have over 95000 player names and wanted a hyperlink for them so I can just hit the hyperlink to go to there stats.


----------



## HaHoBe (Dec 7, 2022)

Hi alan myers,

to me it sounds that a database would be far better suited for this topic than Excel.

And if you found a way to have more hyperlinks on one worksheet than the statement says why don't you share how you managed to do so?

Holger


----------



## alan myers (Dec 9, 2022)

here is how I found out to get around the problem

put names in column a
put hyperlinks in column b
in column c put =HYPERLINK(B1,A1)
and copy down


----------



## alan myers (Dec 20, 2022)

HaHoBe said:


> Hi alan myers,
> 
> a correction to the code (theoretical it should act like this):
> 
> ...


i  have 596000 hyperlinks on one sheet and counting fyi


----------

