Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtered list gives wrong range col (Excel 2003)

    I cannot figure out what is happening here. Ultimately, I want to filter a list, copy the subset of resulting data, and paste the subset of data somewhere else. However, when I filter for the second value in the list, I get back the wrong range of data- it adds one to the column.

    I call "Append Data" which filters a list based on the string "DSTRBTN". Within that procedure I call a Range procedure that identifies the entire range of data on the sheet. I can also tell it what row and column to start at.

    When I call the range after filtering for the first value, it works fine. But when I select the second unique item in the filter, it adds one to the column.

    Why cant I get the same range after I filter?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtered list gives wrong range col (Excel 2003)

    Apparently Find doesn't return the expected result. Why not use UsedRange?

  3. #3
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered list gives wrong range col (Excel 2003)

    I am going to work with a long list of data and want to take certain chunks based on the passed in variable string. The filtering method is supposed to be real fast and it gets me the data set(s) I want easily. I experienced a similar problem in the past with hidden columns, but I eventually was forced to unhide all columns, take the data chunk, then return it back. But here, nothing is hidden and then I was just taking a chunk based on the first data item in the filtered list. I tried resorting the data, and then Top 10, being second, runs into the same issue.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtered list gives wrong range col (Excel 2003)

    I understand the need to determine the last row and column, but the first row and column are more or less fixed, aren't they?

  5. #5
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered list gives wrong range col (Excel 2003)

    Sorry, I didnt explain that. In the past I have used this revised range proc (cant remember where it came from originally) to grab various static chunks, based on a starting location. So I might run a CopyAndPaste proc calling the range "Call DetermineUsedRange(usedRng, 2, 6), to get the chunk starting at row 2, column 6. Usually I use rows variable to bypass headers in the data and the column variables to move past bad or irrelevant preceding data columns (like a date), but that need to be in the data. That is how I ran into this column issue when filtering. I thought this was a pretty good solution to a difficult problem, but I guess I will have to work out a different solution?

    To sum it up, reuse.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtered list gives wrong range col (Excel 2003)

    Wouldn't it be safe to assume that the data begin in cell A1? You can then offset the desired number of rows (to bypass headers) and columns (to bypass bad or irrelevant data).

  7. #7
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered list gives wrong range col (Excel 2003)

    It has been a while but I think I had problems with that and wound up going to ranges. I guess Ill go back and try it again. Thanks. I was really curious if I did something wrong here or what exactly was happening.

Posting Permissions

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