Macro to group & hide rows

[VegasNath]

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

[HansV]

What exactly do you mean by "group" here?

[VegasNath]

Group - Tie a range of cells together so that they can be collapsed or expanded. (From the ever so useful screen-tip in xl2010) :)

[HansV]

Have the data already been sorted the correct way? If not, the rows can't be grouped.

[VegasNath]

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.

[HansV]

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

[VegasNath]

Thankyou very much, I was trying but was not even close.