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

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » AutoFilter and finding the last row (XL97 + Others?)

    Author
    Topic
    #384257

    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:

    Worksheets("Sheet1").Range("A65536").End(xlup).offset(1,0).select

    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:

    If Worksheets("Sheet1").FilterMode then Worksheets("Sheet1").ShowAllData
    Worksheets("Sheet1").Range("A65536").End(xlup).Offset(1,0).Select

    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

    Viewing 1 reply thread
    Author
    Replies
    • #658633

      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

      • #659153

        I use

        NextAvailableRow = mySheet.UsedRange.Rows.Count + 1

        is there anything wrong with this?

        Jambo

        • #659159

          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.

        • #659179

          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.

      • #659180

        What is also a little surprising is that:

        Cells.Find(what:="*", SearchOrder:=xlbycolumns, SearchDirection:=xlPrevious).Column
        

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

        Steve

    • #659493

      Do you have a method yet to re-establish the filter that was in place prior to running your code? confused

      • Read & save filter settings
      • Remove filter
      • Add data
      • 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, Criteria1:=”0″, Operator:=xlAnd

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

      • #659814

        I am added a reply to this a new Post ( post 233332) 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

    Viewing 1 reply thread
    Reply To: AutoFilter and finding the last row (XL97 + Others?)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: