Expand Specific Group of Rows using variable range

ElanKattan

New Member
Joined
May 15, 2018
Messages
1
I'm having trouble writing a VBA code to expand a set of rows that are collapsed in a group. While the absolute row numbers are variable, I'm able to obtain the top and bottom row #s that all my desired rows to expand would be contained between, as those rows always have a specific word in column A that I can find and extrapolate the row #. For example, finding the word "Accruals":

With ActiveSheet
Set FindTopRow = .Range("A:A").Find(What:="Ticker", LookIn:=xlValues, LookAt:=xlWhole)
End With
Dim TopRow As Long
TopRow = FindTopRow.Row


(Same code for lower limit, searches for the word "Accruals", sets the Long as "BottomRow")

I need all the rows in between the highest and lowest row to be expanded for the rest of my code to work. I've been trying to play around with the ShowDetail function, but I can't get it to run without an error. Essentially, I'm looking for a code that instructs all rows between two variable rows ("TopRow" and "BottomRow") to expand if not already expanded.

I would NOT like to expand all groups on the worksheet - I am able to do that, but I'm looking to only expand the group of rows in question.

Thanks in advance! Let me know if I can provide any more detail.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,223,958
Messages
6,175,642
Members
452,663
Latest member
MEMEH

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top