VBA: Runtime error '6' Overflow [SOLVED]

[inddon]

Hello There,

I have a table in a worksheet and have defined the below procedure. When I select the entire worksheet, I get the error message 'Overflow'. When I select multiple rows, it works fine.

Could you please advise, how to overcome the above error?

Thanks & regards
Don

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim tb As ListObject, CBuyerName As String, CBuyerVATNumber As String



'On Error Resume Next

If Target.Count > 1 Then Exit Sub
  Target.Calculate

  Application.ScreenUpdating = False
  Application.EnableEvents = False
  'Cells.RowHeight = 15
  If Not Intersect(Target, Range("WRMWire")) Is Nothing Then
  Range(Range("z1").Value).RowHeight = Range("z2").Value
  Range("Z2") = Target.RowHeight
  Range("Z1") = Target.Address
  Target.RowHeight = 25
  'Make current row font size 11
  'Rows(1).Font.Size = 10.5
  End If

  Application.ScreenUpdating = True
  Application.EnableEvents = True
End Sub

 

[Hui]

Don

When selecting the whole worksheet, You are selecting 17,179,869,184 cells
The count property of a range is probably a Long data type and is hence limited to numbers up to 2,147,483,647 which equates to 131,000 rows

I'd change your code as per below to address the issue

Old Code

'On Error Resume Next
If Target.Count > 1 Then Exit Sub

New Code

'On Error Resume Next
If Target.Address = "$1:$1048576" Then Exit Sub
If Target.Count > 1 Then Exit Sub

or

'On Error Resume Next
If Target.Rows.Count > 131000 Then Exit Sub
If Target.Count > 1 Then Exit Sub

 

[Marc L]

Hi,

since Excel 2007, CountLarge property can handle ranges up to the maximum size for a worksheet …
​

 

[inddon]

Hi,

since Excel 2007, CountLarge property can handle ranges up to the maximum size for a worksheet …
​

Thank you Marc

 

[inddon]

Don

When selecting the whole worksheet, You are selecting 17,179,869,184 cells
The count property of a range is probably a Long data type and is hence limited to numbers up to 2,147,483,647 which equates to 131,000 rows

I'd change your code as per below to address the issue

Old Code

'On Error Resume Next
If Target.Count > 1 Then Exit Sub

New Code

'On Error Resume Next
If Target.Address = "$1:$1048576" Then Exit Sub
If Target.Count > 1 Then Exit Sub

or

'On Error Resume Next
If Target.Rows.Count > 131000 Then Exit Sub
If Target.Count > 1 Then Exit Sub

Thank you Hui

 

[Hui]

Don

As per Marc's comment, you can use

If Target.Countlarge > 1 Then Exit Sub

 

[inddon]

Don

As per Marc's comment, you can use

If Target.Countlarge > 1 Then Exit Sub

Thanks Hui,

Could you please tag the post as [Solved] for me.

Regards
Don