Results 1 to 8 of 8
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    AutoFilter and finding the last row (XL97 + Others?)

    It is a relatively common practice to add new data to workbook by finding the last row in a worksheet and then going to the next row. In VB this can be done many ways, a popular way by going to the final row of the sheet and hitting end up in code, then going down one. Something like:

    <pre>Worksheets("Sheet1").Range("A65536").End(xlup ).offset(1,0).select</pre>


    This usually works better than starting at the top and going down, in case the data has some blank cells contained in the set of data in that column.

    This method may be WRONG if the dataset has an autofilter active and the list is filtered. If for example the last cell that is not blank in col A is A66, and the list is FILTERED so that the last cell viewed is A45, when the above code is run, cell A46 will be selected NOT A67 (Worksheets("Sheet1").Range("A65536").End(xlup) will get you to A45 NOT A66, the offset will take you to A46).

    This can be disastrous in that you could start overwriting data!

    To prevent this, if you use a filtered list, is to make sure that the data is not filtered (the filter can be active, but all the rows should be showing). This can be done with something like:

    <pre>If Worksheets("Sheet1").FilterMode then Worksheets("Sheet1").ShowAllData
    Worksheets("Sheet1").Range("A65536").End(xlup).Off set(1,0).Select</pre>


    This line can be used even if there is no autofilter since if the autofilter is ON and nothing is filtered OR the autofilter is not selected, the FilterMode is FALSE.

    Just something to watch out for and be aware of.

    Steve

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: AutoFilter and finding the last row (XL97 + Others?)

    Steve,

    Very useful information. It even applies when using...

    Cells.Find(what:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    I thought the above was impervious.

    Thanks,

    Jim Cone
    San Francisco, CA

  3. #3
    Star Lounger
    Join Date
    Nov 2002
    Location
    Port Talbot, Wales
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter and finding the last row (XL97 + Others?)

    I use

    NextAvailableRow = mySheet.UsedRange.Rows.Count + 1

    is there anything wrong with this?

    Jambo

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter and finding the last row (XL97 + Others?)

    Not as long as your data starts on row 1.
    Test:

    Open a blank sheet and enter something on row 3. Now run your code.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter and finding the last row (XL97 + Others?)

    Not as long as your data starts at row 1 AND there are no blank rows at the end of the data (you have deleted rows and not saved the file). If either of those are not true, that will not give you the row you want.
    Legare Coleman

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: AutoFilter and finding the last row (XL97 + Others?)

    What is also a little surprising is that:
    <pre>Cells.Find(what:="*", SearchOrder:=xlbycolumns, SearchDirection:=xlPrevious).Column
    </pre>


    also has problems with Autofilter. It can fail even with the items in the VISIBLE rows!

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: AutoFilter and finding the last row (XL97 + Others?)

    Do you have a method yet to re-establish the filter that was in place prior to running your code? <img src=/S/confused.gif border=0 alt=confused width=15 height=20><UL><LI>Read & save filter settings
    <LI>Remove filter
    <LI>Add data
    <LI>Re-apply prior filter[/list]Here is one of the macros that I use that among other things goes to the range being filtered, removes the filter (unnecessary step, perhaps?), and sets the new filter. How can the existing filter be read into a variable to be reapplied later?

    Private Sub MacCopyAll()
    '
    ' Filter & copy all sales data
    '
    Application.ScreenUpdating = False
    Worksheets("Entries - All").Visible = True
    Application.Goto Reference:="Amount_All"
    Selection.AutoFilter Field:=5
    Selection.AutoFilter Field:=5, <font color=red>Criteria1:="<>0", Operator:=xlAnd</font color=red>

    Range("RouteSales_All").Copy
    Application.Goto Reference:="Period"
    Worksheets("Entries - All").Visible = False
    Application.ScreenUpdating = True
    End Sub

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: AutoFilter and finding the last row (XL97 + Others?)

    I am added a reply to this a new Post ( <post#=233332>post 233332</post#>) since I feel it is more than a continuation of this topic: Your question, I think, is actually a separate topic that deserves its own post and it's own thread.

    Steve

Posting Permissions

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