Unhide rows macro?

[BMWZ8Bond]

I'll make this quick:
I have a workbook where sales reps enter rows of prospective customers and a manager's work book that pulls the totals from each of these workbooks. The problem is that the mangers workbook has to reference the total row from each of the sales reps pages.

The problem is this:
Each month the # of prospects varies. We had a macro that would add rows for the reps (very excel illiterate), but then that pushed the total row down and the managers workbook wouldn't update unless it was opened at the same time on the same machine (never going to be the case). So I thought I could just populate the sheet with an great amount of rows, then hide them so the total row never actually changes. Then have a macro that "adds" really just unhides (makes it visible?) 1 row before the "total" row.

Column b contains the word "Total" if that helps.

Thanks guys.

 

[BMWZ8Bond]

Bump, hopefully somebody can help me out today.

 

[schielrn]

Sub test()
For i = 1 To 60000
If (Cells(i, 2).Value = "total") Then
Row(i - 1).Hidden = False
Exit Sub
End If
Next i
End Sub

That will unhide the row before the row total.

 

[HalfAce]

Hello BMWZ8Bond,
If I understand what you're looking for then you might try something like this.
It simply looks for the string 'Total' in column B (whether that row's hidden or not) then loops
up through the rows looking for the first visible row it finds and unhides the row below that.
It should work fine as long as there's always going to be at least one visible row above
the Total row.
(If that's not always going to be the case, post back & we'll figure out something else.)

Sub UnhideOneMoreRow()
Dim TotalRow As Long
TotalRow = Range("B:B").Find("Total").Row
For ThisRow = TotalRow - 1 To 1 Step -1
  If Rows(ThisRow).Hidden = False Then
    Rows(ThisRow + 1).Hidden = False
    Exit For
  End If
Next ThisRow
End Sub

Hope it helps.

 

[BMWZ8Bond]

Perfect. Thanks so much for your help that's exactly what I needed! There will always be a visible row above the total row. However, this won't lock up if there are no more hidden rows will it? (let's say they've unhidden all the rows they can? An error message is fine, as long as it won't crash the file. Ha.

 

[HalfAce]

Most welcome.

However, this won't lock up if there are no more hidden rows will it? (let's say they've unhidden all the rows they can? An error message is fine, as long as it won't crash the file.

Nope. If all the rows above the one with Total in column B are already visible there just
won't be any change.

(We can throw in a message stating there are no more rows available if you like though.) :wink: