Microsoft Excel - runtime error 6 overflow vba excel Cells(Rows.Count, 1).End(xlUp).Row

Asked By Cherifa Hima on 22-Jan-13 09:28 AM
Hi,

My code worked fine until I the number of lines in excel file get bigger:114202 lines. When I get to the code :lrow4 = Cells(Rows.Count, 1).End(xlUp).Row I got runtime error 6 overflow. How can I fix that? Thanks.

Sub Summarizewithmatchandsoft()
Application.EnableEvents = False
  
   Dim lrow4 As Integer
Dim wsh As Worksheet

Set wsh = Sheets("Markups MatchSoft")

 Sheets("Price analysis").Select
    Rows("2:2").Select
    Selection.AutoFilter
Sheets("Query").Select
lrow4 = Cells(Rows.Count, 1).End(xlUp).Row

Range("A9:Y" & lrow4).Select
    'Range("A9", Range("V65536").End(xlUp)).Select
    Selection.Copy
    Sheets("Price analysis").Select
   
   Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Application.CutCopyMode = False

    Range("Z3:AR3").Select
      Selection.Copy
   Dim lrows As Integer
lrows = Cells(Rows.Count, 1).End(xlUp).Row
Range("Z4:AR" & lrows).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("Price analysis").Select
Range("J3", Range("J65536").End(xlUp)).ClearContents
Range("N3", Range("N65536").End(xlUp)).ClearContents
Range("R3", Range("R65536").End(xlUp)).ClearContents
Range("V3", Range("V65536").End(xlUp)).ClearContents
    Range("A2:AR28").Select
    Range(Selection, Selection.End(xlDown)).Select
   
    With ActiveWorkbook.Worksheets("Price analysis").Sort
      .SetRange Range("A2:AR9709")
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
    Range("A3").Select
    
Sheets("Price analysis").Select
    Rows("2:2").Select
    Selection.AutoFilter
    ActiveWindow.SmallScroll ToRight:=4
    ActiveSheet.Range("A2", Range("AS65536").End(xlUp)).AutoFilter Field:=30, Criteria1:= _
      "<>#NUM!", Operator:=xlAnd
    ActiveWindow.SmallScroll ToRight:=16
    ActiveSheet.Range("A2", Range("AS65536").End(xlUp)).AutoFilter Field:=43, Criteria1:=">=10%" _
      , Operator:=xlAnd, Criteria2:="<=150%"
    ActiveWindow.LargeScroll ToRight:=-1
    Cells.Select
    Selection.Copy
    wsh.Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=False
      Application.CutCopyMode = False

    Range("A3").Select
   
   
   Application.ScreenUpdating = True
    Call Comp
End Sub


John D replied to Cherifa Hima on 22-Jan-13 10:25 AM
Hi
Try changing this line:  Dim lrow4 As Integer to  Dim lrow4 As double
Cherifa Hima replied to John D on 22-Jan-13 11:49 AM
Why double?
Cherifa Hima replied to John D on 22-Jan-13 01:11 PM
Thanks it worked.
John D replied to Cherifa Hima on 22-Jan-13 03:56 PM
You're welcome
The range of values for "Integer" is between -32768 to 32767
You could have used "Long" and the range for Long is -2,147,483,648 to 2,147,483,647
and "Double" well the number is so big, you will not need to worry about the size.