Announcement

Collapse
No announcement yet.

Selecting range withing filtered column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Selecting range withing filtered column



    Hello all and Thank you in advanced

    I am trying to select all visible cells in a column that has been filtered. The "Delta_length" variable is 56 and when the column is filtered how i want it there are exactly 56 rows. But when i pass through "Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Delta_length, 0)).SpecialCells(xlCellTypeVisible).Select"
    I get a selection from the first visible cell down 56 rows of the WHOLE worksheet, not the filtered rows like I require. Please help me identify how i can make the range work only on the visible filtered cells. ***The "Delta_length" variable may not be 56 next time i run this report.

    Dim Delta_length As Long
    Delta_length = Range("AQ1").Value
    'Next Portion locates first Visible cell in the filtered column
    Range("B2").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
    ActiveCell.Offset(1, 0).Select
    Loop
    'Next portion should select from first visible cell in filtered column 56 visible rows down. But does not
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Delta_length, 0)).SpecialCells(xlCellTypeVisible).Select

  • #2
    Hello,

    Once you have applied your filter ...

    Code:
    Dim rFiltered As Range
    Set rFiltered = ActiveSheet.AutoFilter.Range
    rFiltered.Offset(1, 0).Resize(rFiltered.Rows.Count - 1).Select
    Hope this will help
    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      Carim,

      This selected all cells in the report to the last used column. Which will work in some of the situations i need to use this. So thank you a ton.

      But how could i adjust this to select only the column that has the active filter?

      Comment


      • #4
        Glad this is helping you out ...

        Regarding the column which is being used to filter ... most probably ... you do have within your macro ...

        Code:
        Dim rFiltered As Range
        Set rFiltered = ActiveSheet.AutoFilter.Range
        ' If you need to restrict the Range to Column B
        rFiltered.Offset(1, 0).Resize(rFiltered.Rows.Count - 1).Columns(2).Select
        If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

        Comment


        • #5
          This works amazing, I am assuming that the .Columns(#) addition will always reference the amount of columns from the first column of the worksheet. I am going to look into this a bit more to understand how it works, I may pop back to ask any questions i am not understanding. But for the purposes of answering my initial question, you did. Thank you very much.

          Comment


          • #6


            Pleased to see this answers your questions

            Thanks a lot .... for your Thanks ...AND for the Like ...
            If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

            Comment

            Working...
            X