locked
how to select next visible cell in excel using vba RRS feed

  • Question

  • Hi there,

    Using vba code, I want to select a cell that has been filtered?

    ie.
    Range("A1").select
    Activecell.offset(2,0).select - this selects Range("A3") but is not
    visible.

    I want it to consider visible cells only.

    Any ideas?


    Thanks & Regards, Leela Prasad
    Wednesday, October 19, 2011 5:40 AM

Answers

  • I think you have to have some kind of loop in order to do that.  But you'd only need to loop through the cells in the "next" row, and not the entire table.

    However, it is so rare to need to be able to either Select or Activate a cell, that a simpler solution is probably available.

    For example:

    ==========================
    Option Explicit
    Sub SelectNextVisibleCell()
     Dim rVis As Range, c As Range
    Set rVis = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)
    Set c = Intersect(ActiveCell, rVis)
    If Not c Is Nothing Then
    'code to save current locked and protected state
        Cells.Locked = True
        rVis.Locked = False
        Sheet1.Protect
        Do
            Selection.Next.Select
        Loop Until Selection.Column = c.Column
    'code to restore previous locked and protected states
        Sheet1.Unprotect
    End If

    End Sub
    ============================

     

     


    Ron
    • Proposed as answer by ghosgrammer Tuesday, September 23, 2014 7:36 PM
    • Marked as answer by BKNLPRASAD Wednesday, September 24, 2014 2:02 PM
    Wednesday, October 19, 2011 12:52 PM

All replies

  • Your question is ambiguous, explain what you want to do if A3 is not visible, eg select the next cell above or below A3 that is visible. If you say above, and A2 is not visible, then what.

    Peter Thornton

    Wednesday, October 19, 2011 9:39 AM
  • Hi Leela:

     

    Just move down the column until you find a row that is not hidden:

     

    Sub NextVisible()
    Dim r As Range
    Set r = ActiveCell
    For i = 1 To Rows.Count
        Set r = r.Offset(1, 0)
        If r.EntireRow.Hidden = False Then
            r.Select
            Exit Sub
        End If
    Next
    End Sub


    gsnu201109
    Wednesday, October 19, 2011 10:07 AM
  • can we do without using LOOPING

    bcoz my next visible is in cell "a20000" thats means LOOP has to be excuted 20000 times

    its time taking process


    Thanks & Regards, Leela Prasad
    • Proposed as answer by morfiem Thursday, March 12, 2015 2:12 AM
    • Unproposed as answer by morfiem Thursday, March 12, 2015 2:12 AM
    • Proposed as answer by morfiem Thursday, March 12, 2015 2:35 AM
    Wednesday, October 19, 2011 10:09 AM
  • i did not get you Peter

    Thanks & Regards, Leela Prasad
    Wednesday, October 19, 2011 10:10 AM
  • Perhaps using SpecialCells

    (you would still have a LOOP, but it would be MUCH smaller)


    gsnu201109

    Wednesday, October 19, 2011 10:29 AM
  • On Wed, 19 Oct 2011 05:40:22 +0000, BKNLPRASAD wrote:
     
    >Hi there,
    >
    >Using vba code, I want to select a cell that has been filtered?
    >
    >ie.
    >Range("A1").select
    >Activecell.offset(2,0).select - this selects Range("A3") but is not
    >visible.
    >
    >I want it to consider visible cells only.
    >
    >Any ideas?
    >
    >Thanks & Regards, Leela Prasad
     
    Why do you need to Select a cell?  Usually in VBA there is no need to Select a cell. 
     
    To cycle through the visible cells, you can do something like:
     
    =================
    Sub Visible()
        Dim rVis As Range, c As Range
    Set rVis = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)
     
    For Each c In rVis
        'Debug.Print c.Address, c.Value
        'or execute your routines
    Next c
       
    End Sub
    ====================
     
    If you explain what you need to do after you Select one of these cells, a more focused routine can be provided.
     

    Ron
    Wednesday, October 19, 2011 10:46 AM
  • But Ron, suppose the ActiveCell is on a visible row and the only requirement is Select the next visible cell below it.  Can this be done without a loop??
    gsnu201109
    Wednesday, October 19, 2011 11:23 AM
  • I think you have to have some kind of loop in order to do that.  But you'd only need to loop through the cells in the "next" row, and not the entire table.

    However, it is so rare to need to be able to either Select or Activate a cell, that a simpler solution is probably available.

    For example:

    ==========================
    Option Explicit
    Sub SelectNextVisibleCell()
     Dim rVis As Range, c As Range
    Set rVis = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)
    Set c = Intersect(ActiveCell, rVis)
    If Not c Is Nothing Then
    'code to save current locked and protected state
        Cells.Locked = True
        rVis.Locked = False
        Sheet1.Protect
        Do
            Selection.Next.Select
        Loop Until Selection.Column = c.Column
    'code to restore previous locked and protected states
        Sheet1.Unprotect
    End If

    End Sub
    ============================

     

     


    Ron
    • Proposed as answer by ghosgrammer Tuesday, September 23, 2014 7:36 PM
    • Marked as answer by BKNLPRASAD Wednesday, September 24, 2014 2:02 PM
    Wednesday, October 19, 2011 12:52 PM
  • I have a list that is filtered for instance according to the contents of column R.

    I'd like to be able to copy the contents of the first visible row in the active column to all visible cells in that column.

    I thought a dynamic range name might do the trick.

    I have a range named "this column" which has the following formula:

     =OFFSET(G$1,1,0,COUNTA($A:$A)-1,1)

    That is, it extends from row 1 of the current column (because there is no "$" in front of the G) to the last used row of column A.

    Problem is the range "ThisColumn" refers to g1:g20 when it is selected outside of the macro, but while the macro is running it refers to A1:A20.

    Any suggestions?

    I may try creating the range name inside the macro or studying up on range intersect.

    Sub CopyToVisible()

        Selection.Copy
        Application.CutCopyMode = False
        ActiveWorkbook.ActiveSheet.Range("ThisColumn").Select
       
        Selection.SpecialCells(xlCellTypeVisible).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False

    End Sub

    Friday, June 1, 2012 1:57 PM
  • This helped me a lot thanks.

    Wednesday, December 18, 2013 1:31 PM
  • Thanx alot. I have been strugling like anything to know about this
    Tuesday, September 30, 2014 6:39 PM
  • Call MyMacro()

    ActiveCell.Offset(1, 0).Activate

    Do Until Selection.EntireRow.Hidden = False
    If Selection.EntireRow.Hidden = True Then
    ActiveCell.Offset(1, 0).Activate
    End If
    Loop

    Friday, October 3, 2014 6:49 PM
  • I have googled a lot, but found only looping solutions.

    After a few minutes of thinking I figured out on my own, I think, brilliant :) solution.

    So can we do this without looping -I think yes.

    We can use the simple method:

    SendKeys ("{ENTER}")

    or

    SendKeys ("{DOWN}")

    Both of those lines above will do the same: select next visible cell (it will simulate 'down arrow' or 'enter' pressing keys on your keyboard).

    I tested it, in my workbook it's very fast movment to next visible cell.

    Do you think this is better than looping?

    • Proposed as answer by Damir Sucic Sunday, November 23, 2014 12:42 AM
    Tuesday, November 18, 2014 10:25 AM
  • Hi BKNLPRASAD:

    Yes we can.

    sorry for answer now but so far I read your concern is surely no longer need but the public in case anyone seeking more information on the same topic.
    Perhaps my answer sounds very simple or very silly but it works without a loop or cycle. I always look for a simpler way and that can mean spending a whole day thinking about my problem and all possible solutions and choose the best or create my own. good enough to explain this is just to exchange information if anyone can help later or viseversa.

    what I did was
    1. perform filtering
    2. Use ActiveSheet.Cells.SpecialCells (xlCellTypeVisible) .copy
    3. then use a hidden sheet called temporal to paste the information and use it for the time without any problem and immediately after use delete to use that sheet and so after spending only three lines of code to do something with the cycle of "if visible then "could take 20,000 repetitions as you mentioned.

    Please if you or someone else even read this, I wonder if anyone worked or not to continue helping or keep quiet.

    My main answer is "YES WE CAN"

    Greetings I hope someone answers.


    • Edited by morfiem Thursday, March 12, 2015 2:36 AM
    Thursday, March 12, 2015 2:34 AM
  • Here's a lightning-quick way to grab the value of the first not-blank, visible cell in a filtered table column. You can run it on any worksheet in the active workbook without having the worksheet active.

    Just find and replace Dbl_EXAMPLE, SHEET_NAME, TABLE_NAME and COLUMN_NAME with your values. 

    Dim Dbl_EXAMPLE as Double
    
    Dbl_EXAMPLE = Sheets("SHEET_NAME").Range("TABLE_NAME[[#All], COLUMN_NAME]]").SpecialCells(xlCellTypeVisible).Find _
        (What:="*", After:=Sheets("SHEET_NAME").Range("TABLE_NAME[[#Headers],[COLUMN_NAME]]"), _
        LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Value2

    No loops, no selections, no activations required because it's built off of Excel's "Find" functionality. 

    I recommend using tables whenever possible, as they have variable named ranges built-in that are easy to reference. 

    Here's another example, not using tables. Just replace values as needed and you may want to change "A1" to a different cell:

    Dim Dbl_EXAMPLE as Double
    
    Dbl_EXAMPLE = Sheets("SHEET_NAME").Cells.SpecialCells(xlCellTypeVisible).Find(What:="*", _
        After:=Sheets("SHEET_NAME").Range("A1"), LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Value2

    You can replace ".Value2" at the end to be whatever you're looking for. Examples:

    • .Formula
    • .Row
    • .Column

    The list goes on




    • Edited by BradleyRR Saturday, April 29, 2017 3:33 PM Just tidying things up
    Saturday, April 29, 2017 3:31 PM
  • I also have been looking for nice method to select a cell in a filtered range for processing some action only for filtered rows (visible rows). I tried 3 or 4 different ways but they worked partially - worked but some side effect I didn't like to have. Today after I have read other people's idea, I came up with a way that will will work very nicely depending on the actual goal.

    Sub Example()
        i = 1
        For Each ce In Columns(1).SpecialCells(xlCellTypeVisible)
            If i > 100 Or ce.Value = "" Then Exit For
            Cells(ce.Row, 2) = ce.Value & "AA"
        Next
    End Sub

    Thursday, February 8, 2018 12:20 AM
  • Since Programming is all about doing the most simple to achieve the intended result, this is glorious. Sometimes one thinks just too complicated :D. THanks for this nice brainreset ^^
    Friday, August 17, 2018 2:54 PM