Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Select first visible cell under the header row after applying autofilter

  1. #1
    Board Regular
    Join Date
    Jun 2008
    Location
    Pune, India
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Select first visible cell under the header row after applying autofilter

    Hello,

    I need vba code to Select first visible cell below the header row after applying autofilter on column Q of the data. Can somebody help me on this?

    I tried
    http://www.mrexcel.com/forum/showthread.php?t=403989

    but it does not work. Probably because column Q is filtered to show only blank cells,

    Can somebody help me on this?

    Thanks,
    awagdarikar
    Last edited by awagdarikar; Jul 7th, 2011 at 08:21 AM. Reason: To make subject more specific
    I use Excel 2007 on XP

  2. #2
    Board Regular
    Join Date
    May 2011
    Location
    Russia
    Posts
    2,834
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select first visible cell under the header row after applying autofilter

    Assuming that filter is in the first row.
    Code:
    Function AllVisibleCells() As Range
        Set AllVisibleCells = Range("Q2:Q" & Cells(Rows.Count, "Q").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    End Function
    Last edited by Sektor; Jul 7th, 2011 at 08:23 AM. Reason: Corrected

  3. #3
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,093
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select first visible cell under the header row after applying autofilter

    To select the first visible cell in Column Q, maybe...

    Code:
    Sub SelectFirstVisibleCell()
    
        Range("Q2", Cells(Rows.Count, "Q").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
        
    End Sub
    

  4. #4
    Board Regular
    Join Date
    Jun 2008
    Location
    Pune, India
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select first visible cell under the header row after applying autofilter

    Hi Domenic,


    Sub SelectFirstVisibleCell() Range("Q2", Cells(Rows.Count, "Q").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select End Sub
    doesn't work. It selects the Q1 cell. For your reference my data is as below. i apply an autofilter in cell Q1 (Parent Name) to select blank cells.

    to Cavityto platingParent NameCkt Name wo Suffix
    7SNSH2011_21546
    6SNSH2011_21947
    14SN115
    14SN115
    14SN116
    14SN116
    40SN117
    94SN117


    My code for fliter is as follows

    Sheets("Input for Pivot").Select
    'removes AutoFilter if one exists
    Worksheets("Input for Pivot").AutoFilterMode = False


    Range("A1").Select
    Selection.CurrentRegion.Select
    ActiveWorkbook.Worksheets("Input for Pivot").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Input for Pivot").Sort.SortFields.Add Key:=Range( _
    "Q2:Q" & Range("A1048576").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("Input for Pivot").Sort
    .SetRange Range("A1:R" & Range("A1048576").End(xlUp).Row)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Selection.AutoFilter
    Application.Goto Reference:="R1C17"
    ActiveSheet.Range("$A$1:$R$" & Range("A1048576").End(xlUp).Row).AutoFilter Field:=17, Criteria1:="="
    End Sub

    Thanks,
    awagdarikar
    I use Excel 2007 on XP

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    38,045
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Select first visible cell under the header row after applying autofilter

    awagdarikar

    I'm wondering what you are going to do after selecting that cell, given that you rarely need to select a cell/range to work with it in vba and selecting slows your code.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    Board Regular
    Join Date
    Jun 2008
    Location
    Pune, India
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select first visible cell under the header row after applying autofilter

    Thanks for your reply Peter,

    As this column Parent Name is used as first column in the pivot in next process blank cells in it cause a huge problem. So I first apply filter to this column to make all blank cells visible one below the other. This is because the blank cells are scattered across this column. In the next step i put a unique reference text through formula in all selected blank cells which is based on other column in the same row.
    My method is to put formula in the first visible cell and then copy it to rest of the cells. I use code to pu formula as follows

    HTML Code:
    ActiveCell.FormulaR1C1 = _
            "=IF(CODE(LEFT(R[-14]C[-9],1))=83,""(""&R[-14]C[-9]&"")"",R[-14]C[-9]&"" (Not_Shielded)"")"
        Selection.FillDown
    Please suggest,
    awagdarikar
    I use Excel 2007 on XP

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,797
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    17 Thread(s)

    Default Re: Select first visible cell under the header row after applying autofilter

    Does this function help you any? It returns the row number of the first visible row in an autofiltered range (it returns 0 if all the cells in the autofiltered range are hidden).
    Code:
    Function GetFilteredRangeTopRow() As Long
      Dim HeaderRow As Long, LastFilterRow As Long
      On Error GoTo NoFilterOnSheet
      With ActiveSheet
        HeaderRow = .AutoFilter.Range(1).Row
        LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
        GetFilteredRangeTopRow = .Range(.Rows(HeaderRow + 1), .Rows(Rows.Count)).SpecialCells(xlCellTypeVisible)(1).Row
        If GetFilteredRangeTopRow = LastFilterRow + 1 Then GetFilteredRangeTopRow = 0
      End With
    NoFilterOnSheet:
    End Function

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    38,045
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Select first visible cell under the header row after applying autofilter

    Quote Originally Posted by awagdarikar View Post
    Thanks for your reply Peter,
    In the next step i put a unique reference text through formula in all selected blank cells which is based on other column in the same row.
    I'm not sure how. The R1C1 formula you provided refers to the row 14 rows above the row the formula occupies.

    What would the actual formula in the sheet be in Q40 if that, say, was the first blank cell in column Q?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #9
    Board Regular
    Join Date
    Jun 2008
    Location
    Pune, India
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select first visible cell under the header row after applying autofilter

    The formula code is generated for row 2.

    The reason is in abcense of the code to select first visible cell under filter I have created a workaround wherein i add a blank row in row 2 and paste this formula in Q2. In next step i delete this row after copying it to all blank cells below it in the range. So i will not get any errors .

    If used for the active cell it should point to the same row.
    I use Excel 2007 on XP

  10. #10
    Board Regular
    Join Date
    Jun 2008
    Location
    Pune, India
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select first visible cell under the header row after applying autofilter

    Thanks for suggestion Rick,

    However this code does not move cursor
    I use Excel 2007 on XP

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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