I am needing a macro that will group and hide all rows from the last used row to 5 where column A does not start with a letter (character). Any idea how I can achieve this?
TIA
Macro to group & hide rows
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Macro to group & hide rows
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Macro to group & hide rows
Group - Tie a range of cells together so that they can be collapsed or expanded. (From the ever so useful screen-tip in xl2010) :)
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro to group & hide rows
Have the data already been sorted the correct way? If not, the rows can't be grouped.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Macro to group & hide rows
Hans, I'm not sure I understand... Does the attached help? I am trying to automate the set-up of sheet 2 from the data in sheet 1, as an example. Group and collapse the group of all rows with a non-numeric character in column A.
You do not have the required permissions to view the files attached to this post.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro to group & hide rows
Try this macro (don't forget to save as a .xlsm):
Code: Select all
Sub GroupEm()
Dim r As Long
Dim s As String
Dim c As String
Dim wsh As Worksheet
Set wsh = Worksheets("Sheet1") ' or ActiveSheet if you prefer
r = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
' Loop backwards
Do While r > 5
s = r - 1
' Inner loop to determine range to be grouped
Do
c = UCase(Left(Range("A" & s), 1))
Select Case True
Case c = "", Asc(c) < 65 Or Asc(c) > 90
s = s - 1
Case Else
Range("A" & (s + 1) & ":A" & (r - 1)).EntireRow.Group
r = s
Exit Do
End Select
Loop
Loop
' Collapse
wsh.Outline.ShowLevels RowLevels:=1
End Sub
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Macro to group & hide rows
Thankyou very much, I was trying but was not even close.
Nathan
There's no place like home.....
There's no place like home.....