# Unhide rows macro?

#### BMWZ8Bond

##### New Member
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.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### BMWZ8Bond

##### New Member
Bump, hopefully somebody can help me out today.

#### schielrn

##### Well-known Member
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

##### MrExcel MVP
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.)
Code:
``````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

##### New Member
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

##### MrExcel MVP
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:

Replies
1
Views
255
Replies
2
Views
2K
Replies
13
Views
404
Replies
4
Views
1K
Replies
1
Views
574

### Forum statistics

1,191,485
Messages
5,986,860
Members
440,055
Latest member
CraigTriesHisBest

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back