VB Integer Problems - Integer Number Exceeded

[TrentG]

Hello

I have inherited a macro in a file that needs to copy and paste a set of data. This data will vary in size (only by number of rows).

The function has been defined as "count rows" using the following code.

ctr_Rows = Selection.CurrentRegion.Rows.Count

The problem lies with the fact that ctr_Rows has been defined as an Integer which can only be between -32767 and +32767 in value.

The data set has now reached more than 32767 rows.

How can I define ctr_Rows so that it will copy the data set over 32767 rows?

Extracts from the code / how it has been used is below :

Any help would be muchly appreciated
Kind regards
Trent

=======================================================

'Declare variables
Dim ctr_Rows As Integer

'Select worksheet sh_AR_Data, Select cell A1 and set variable ctr_Rows equal to the number of rows in the dataset
sh_AR_Data.Select
sh_AR_Data.Range("A1").Select
ctr_Rows = Selection.CurrentRegion.Rows.Count

-----------------------------

'Set the heading for column Y to 'Invoiced and Populate the column with formulae to set the value to 'Net Invoiced' if the related line in column X equals 'New'
sh_AR_Data.Range("Y1").Value = "Invoiced"
sh_AR_Data.Range("Y2:Y" & ctr_Rows).Value = "=IF(RC24=""New"",""Net Invoiced"","""")"

 

[VoG]

Try

Dim ctr_Rows As Long