Microsoft Excel - VBA Excel Arrow Up to next visible cell or Offset up

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

You can do something like this:

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

It is a bit klugy, but it will work.

Wally Eye:

Can't do 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

Thanks - Rowland

This might work a bit better, replacing the For...Next loop:

  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

      ws.Cells(LastRow, 2).Select

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

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

    End If
 
    Next ws

A better question, though, might be to ask what you are actually trying to do.  It looks like you want to identify the rows that are used to make up a subtotal, are you going to copy them somewhere?

Still Get "Compile error = Method or Data Member Not Found"  and refers to: FirstRow = ws.Selection.Offset(1, 0).Row

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

Yes. Copying from sourcefile into formatting workbook for consolidation report. This particular copy code, with filebrowser, only needs to pull from one sheet but other variations on the code pull from an array.

It was not liking the ws object.  I switched it around a bit, so as to not actually select cells:

    'Show levels
    ws.Outline.ShowLevels RowLevels:=6

    'Find "555ROAR7777"
    lastrow = ws.Columns(2).Find(What:="*555ROAR7777*", After:=[B1], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row
    For firstrow = lastrow - 1 To 2 Step -1
      If ws.Rows(firstrow).EntireRow.Hidden = False Then
        Exit For
      End If
    Next firstrow
    firstrow = firstrow + 1

I was thinking you might be able to just use the find command to get firstrow as well and not have to do the ShowLevels.  Something like:

firstrow = ws.columns(2).find(what:="*555ROAR7777*", After:=[B1], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row
lastrow = ws.columns(2).find(what:="*555ROAR7777*", After:=[B1], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False).Row -1

but I wasn't sure if your data had the 555ROAR7777 in the detail.  I would think it should, but...

Wally eye:

Thanks, I need to try this.

The find will find the subtotals only, at the end of the detail for the group of data I want.
Unfortunately, the "555ROAR7777" occurs in only 2 locations:
 1) in the last line of the data I need (the subtotal line)
 2) in an offset entry to zero out the amount and transfer it to another account (I don't need this)

Thanks - Rowland

HI Rowland,

I would like to propose this
-------------------------------------
.
.
.
SelectRow=application.WorksheetFunction.Match("555DIVI3232",range("B:B"),0)

range("B"&SelectRow).offset(-1,0).select
.
.
.
---------------------------------------
here we will get only the first matched row then offset to 1 cell above.

Is this answer to your question.

Pichart Y.

Pritchard and folks:

Another tactic:

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 the LastRow is the first find of "555ROAR7777" from B1 down,
then the FirstRow the row below the next line above the LastRow that begins with "*   ".
This also happens to be the next ungrouped, visible line when the grouped rows are collapsed.
Use tilda, right? Maybe I can do a search up? Like:

FirstRow = ws.Range("B:B").Find(What:="~*   ", After:=ws.Range("B" & LastRow) LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlUp, _
    MatchCase:=False, SearchFormat:=False).Row

Would this work? How do I make the SearchDirection go up? How do I refer to a specific cell in the After: section?

 

Also, I tried this as a stand alone macro and it didn't work:

Sub trywhynot()

Dim LastRow As Long
Dim FirstRow As Long

Range("B1").Select

LastRow = Application.WorksheetFunction.Match("*555ROAR7777*", Range("B:B"), 0)
FirstRow = Range("B" & LastRow).Offset(-1, 0).Select

MsgBox LastRow 'Got the correct row #350 (let's say)
MsgBox FirstRow 'Got "-1" which clearly is not a row number

End Sub

 Also, using a simple Macro to test, this worked for the next subtotal after my found value:

NextRow = Columns("B:B").Find(What:="~*   ", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Row

But when I change SearchDirection:= xlNext to SearchDirection:= xlUp it still goes to the next lower row find.

This didn't work either:

NextRow = Columns("B:B").Find(What:="~*   ", Before:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Row

I found it, It Works: xlPrevious

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

Thank you - Rowland