VBA Excel Arrow up to next visible cell or offset up

[Rowland Hamilton]

Folks:

How do I "

ActiveCell.End(xlUp).Select

" without the "End" or

ActiveCell.Offset(-1, 0).Select

to the next visible cell?

I know "

ActiveCell.End(xlUp).Select

" gets me to the next populated cell or end of contiguous data and

ActiveCell.Offset(-1, 0).Select

gets me to the cell right above my active cell, but I need to get to the next visible cell above my active cell, wich is broken up by a collapsed group of cells.

The issue is I have hundreds of grouped cells, I can find the subtotal for the group I want with:

Columns("B:B").Select
                Selection.Find(What:="555DIVI3232", After:=ActiveCell, LookIn:=xlFormulas _
                , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate

But I want the top row of my copy range to be the location of the above subtotal which is the next visible cell above the ActiveCell (note: not labeled as subtotals).

Once I set the top and bottom rows, i can ungroup and copy the data.

Thank you, Rowland

VBA Excel, Arrow keys, grouped rows, visible cells, offset

 

[Rowland Hamilton]

Folks:

I can't do this with my parameters because I can't seem to refer to Active Cells or Selections very well in my code. Here is why: I'm inside of an If statement. I can Activate Cells in SourceWB and even use Active Cell in the middle of the Find Statement but when I try to reference the ActiveCell or Selection, even with ws. in front, it just doesn't work:

Sub AYAYAY()

Dim MasterWB As Workbook
Dim SourceWB As Workbook
Dim Unformatted As Worksheet
Dim HeaderSrc As Range
Dim HeaderDst As Range
Dim rngSrc As Range
Dim rngDst As Range
Dim ws As Worksheet
Dim varFileName As Variant
Dim LastRow As Long
Dim FirstRow As Long
Set MasterWB = ThisWorkbook
Set Unformatted = Worksheets("Master-Incoming")


  varFileName = Application.GetOpenFilename(, , "Please select source workbook:")
    If TypeName(varFileName) = "String" Then
 
     Set SourceWB = Workbooks.Open(Filename:=varFileName, UpdateLinks:=0)

    For Each ws In SourceWB.Sheets(Array("Tab I Want"))
        If ws.Visible <> xlSheetHidden Then

              'Show levels
            ws.Outline.ShowLevels RowLevels:=6
            
            'Find "555ROAR7777"
            ws.Range("B1").Activate
            LastRow = ws.Columns("B:B").Find(What:="*555ROAR7777*", After:=ActiveCell, LookIn:=xlFormulas _
            , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Row
'''''''''''''''''''''''''''
'INSERT REST OF COPY/PASTE CODE HERE
'''''''''''''''''''''''''''

        End If
 
      Next ws
  Else
  End If

End Sub

What I attempted to do was add your suggestion in the middle of this, but I can't reference the end last active cell after the loop is done so can I reference the entire loop as = my FirstRow? Here is my sample code to insert:

'''''''''''''''''''''''''''
'INSERT REST OF COPY/PASTE CODE HERE:
       Do While ws.Columns("B:B").Find(What:="*555ROAR7777*", After:=ActiveCell, LookIn:=xlFormulas _
                , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Offset(-1, 0).EntireRow.Hidden = True
                   
                  ws.Columns("B:B").Find(What:="*555ROAR7777*", After:=ActiveCell, LookIn:=xlFormulas _
                  , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                  MatchCase:=False, SearchFormat:=False).Offset(-1, 0).Select
              Loop
                  ws.Columns("B:B").Find(What:="*555ROAR7777*", After:=ActiveCell, LookIn:=xlFormulas _
                  , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                  MatchCase:=False, SearchFormat:=False).Offset(-1, 0).Select
                   
            FirstRow = ws.Selection.Offset(1, 0).Row

'''''''''''''''''''''''''''
I can't even run this to find out if it works because:

FirstRow = ws.Selection.Offset(1, 0).Row

refers directly to the Selection and is a mismatch. same with

FirstRow = ws.ActiveCell.Offset(1, 0).Row

This was the solution I was offered that I tried to adopt:

Do While ActiveCell.Offset(-1, 0).EntireRow.Hidden = True
      ActiveCell.Offset(-1, 0).Select
    Loop
    ActiveCell.Offset(-1, 0).Select

Thanks - Rowland

 

[Rowland Hamilton]

Also, maybe looping isn't the best way.

 

[Rowland Hamilton]

Lucky for me there was another option and I found it:

FirstRow = Columns("B:B").Find(What:="~*   ", After:=ActiveCell, LookIn:= _
      xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
      xlPrevious, MatchCase:=False, SearchFormat:=False).Offset(1, 0).Row

Each subtotal begins with an asterix and more than 3 blank spaces before the cost center number (like "* ").

That means, since my LastRow is the first find of "555ROAR7777" from B1 down, then the next row beginning with "* " above my LastRow, offset by 1 row is my FirstRow.