Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-28-2021, 02:37 PM
trevorc trevorc is offline Select first NOT hidden row in table Windows 7 32bit Select first NOT hidden row in table Office 2013
Competent Performer
Select first NOT hidden row in table
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default Select first NOT hidden row in table

Hi All,
Trying to get this going, i have code that works fine if i debug and select the first row in a table that has hidden rows, then continues ok.



I need code that after i have filtered my records to select the first row that is not hidden in my table. This what i have so far...

Code:
    Ass_Num = Intersect(acrow, tblColumns("Asset" & Chr(10) & "Number").Range)
    Sheets("backup").Select
    With ActiveSheet
    .ListObjects("Customers").Range.AutoFilter Field:=6, Criteria1:=Ass_Num ' filters rows, but the table looses the focus

        firstRow = .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).row ' this works on another table because its not filtered and the table has the focus
        Range("A" & firstRow).Select
    End With
Reply With Quote
  #2  
Old 11-28-2021, 02:45 PM
Logit Logit is offline Select first NOT hidden row in table Windows 10 Select first NOT hidden row in table Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Does this work :


Code:
Ass_Num = Intersect(acrow, tblColumns("Asset" & Chr(10) & "Number").Range)
    Sheets("backup").Select
    
With ActiveSheet
    .ListObjects("Customers").Range.AutoFilter Field:=6, Criteria1:=Ass_Num ' filters rows, but the table looses the focus

     Columns(1).Cells.SpecialCells(xlCellTypeVisible).Cells(1).Select

End With
Reply With Quote
  #3  
Old 11-28-2021, 03:14 PM
trevorc trevorc is offline Select first NOT hidden row in table Windows 7 32bit Select first NOT hidden row in table Office 2013
Competent Performer
Select first NOT hidden row in table
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

Thanks for the reply,
I have tried that, just now i added - Call Sort_by_Asset_number
this resorts the table and gives it focus and the code works now.
Thanks for helping

regards
Trevor
Reply With Quote
  #4  
Old 11-28-2021, 06:12 PM
Logit Logit is offline Select first NOT hidden row in table Windows 10 Select first NOT hidden row in table Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Glad you have an answer.
Reply With Quote
  #5  
Old 11-29-2021, 07:04 AM
NoSparks NoSparks is offline Select first NOT hidden row in table Windows 10 Select first NOT hidden row in table Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Another option might be to deal with the table only and bypass the header row when looking for the visible rows after filtering.
Code:
Ass_Num = Intersect(acrow, tblColumns("Asset" & Chr(10) & "Number").Range)
Sheets("backup").Select
With ActiveSheet.ListObjects("Customers")
    .Range.AutoFilter Field:=6, Criteria1:=Ass_Num
    Set fRng = .ListColumns(1).DataBodyRange.Cells.SpecialCells(xlCellTypeVisible)
    fRng.Cells(1).Select
    '.Range.AutoFilter
End With
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet CaptainRetired Excel Programming 18 01-04-2018 07:22 PM
Select first NOT hidden row in table Find first row in a table that is not hidden? Officer_Bierschnitt Excel Programming 5 10-31-2015 09:42 AM
Select first NOT hidden row in table programmatically inserting hidden text into a Word 2010 table epid011 Word VBA 16 12-30-2013 11:29 AM
Select first NOT hidden row in table Hidden text in table Angel9520 Word Tables 1 05-02-2012 01:00 AM
Select first NOT hidden row in table Select Text in Table but Table Gets Selected Too RBusiness Word 1 06-07-2011 04:26 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:05 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft