+ Reply to Thread
Results 1 to 5 of 5

Overflow Error

  1. #1
    Registered User
    Join Date
    08-20-2014
    Location
    CLT
    MS-Off Ver
    2010
    Posts
    61

    Overflow Error

    I am attempting to create a macro that will filter a raw dataset and will copy and paste results to a new tab. However, I am only wanting to copy and paste if there is data meeting the filter criteria. Please see below for the filter and code. The underlined code is where I am receiving a Run-Time Error 6 error stating Overflow. I believe this is due to no data meeting the filter critiera. Please advise the best way to taylor the code to make this work.

    ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastCol)).AutoFilter Field:=9, Criteria1:="<>"
    SPTCount = ActiveSheet.Range("A1").End(xlDown).Row
    If (SPTCount > 1) Then
    ......

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Overflow Error

    declare sptcount as long not integer and it's better to work up
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Overflow Error

    How have you declared SPTCount?

    It should be declared as Long.
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    08-20-2014
    Location
    CLT
    MS-Off Ver
    2010
    Posts
    61

    Re: Overflow Error

    Thank you very much for the help. After changing that variable to Long instead of Integer, and modifying the code to the below, everything is working properly.

    Can you please advise why the Long declaration is important in this case?

    ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastCol)).AutoFilter Field:=9, Criteria1:="<>"
    SPTCount = ActiveSheet.Range("A" & LastRow + 1).End(xlUp).Row
    If (SPTCount > 1) Then

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Overflow Error

    Please use code tags with your code.
    Integer -32767 +32767 size(rows)
    Long (Long integer) over-1m +over1m size(rows)
    If your code passes 32767 rows, it will show an error.
    Last edited by AB33; 09-03-2014 at 04:09 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Explanation of the Run-time error '6': Overflow Error
    By mgphill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2012, 10:46 AM
  2. Want to do a while-loop but get error message: run error '6' overflow
    By danzi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-07-2011, 01:48 PM
  3. Msg error (overflow)
    By Mamud in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-25-2011, 10:54 AM
  4. Overflow Error
    By narrowgate88 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-10-2010, 02:08 AM
  5. Overflow error
    By Jim Berglund in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-30-2005, 02:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1