Macro to group rows after end of data, down to row 858

[diygail123]

Hi

Can anyone help with the following code, I am trying to get the macro to group the rows from the end of a list of data in column A (the end will be different each time the macro is ran), down to row 858, which will stay the same each time. What do I replace A135 with to make the macro dynamic please?

Thanks

Gail

Sub grouprows()'
' grouprows Macro
'
Dim activecell As Range
Set activecell = activecell


    Range("A7").Select
    Selection.End(xlDown).Select
    activecell.Offset(1, 0).Select
   
    Range("[COLOR=#ff0000]A135[/COLOR]:A858").Select
    Selection.Rows.Group
    
End Sub

 

[footoo]

Sub grouprows()
Cells.ClearOutline
Range([A7].End(xlDown)(2).Address & ":A858").Rows.Group
End Sub

Or :

Sub grouprows()
Cells.ClearOutline
Range([A7].End(xlDown)(2), [A858]).Rows.Group
End Sub

 

[diygail123]

Hi, both of these bug on the second line (Range etc) - runtime error 1004, Application defined or object defined error. What are the square brackets for?
Thanks

Gail

 

[footoo]

Hi, both of these bug on the second line (Range etc) - runtime error 1004, Application defined or object defined error. What are the square brackets for?
Thanks

Gail

Do you have any data in column A below A7? If not, you will get Error 1004 (also in your original code).
If there is no data, and you want to group A8 to A858 then :

Sub grouprows()
Cells.ClearOutline
If [A8] = "" Then
    [A8:A858].Rows.Group
Else
    Range([A7].End(xlDown)(2), [A858]).Rows.Group
End If
End Sub

Re square brackets, [A7] is the same as Range("A7")

 

[diygail123]

Great thanks, that is doing the outline, but not actually closing the rows up, - how do I make it do that?

Gail

 

[diygail123]

Now this is very strange, the outlining works when I F8 through the macro, but not when I run it?

 

[footoo]

Sub grouprows()
Cells.ClearOutline
If [A8] = "" Then
    [A8:A858].Rows.Group
Else
    Range([A7].End(xlDown)(2), [A858]).Rows.Group
End If
ActiveSheet.Outline.ShowLevels RowLevels:=1
End Sub

 

[diygail123]

Thanks that is now closing the rows up, however, the grouping dosent occur at all when I F5 the macro, but it does when I F8 throught it. Any ideas how to make F5 work?

Thanks

Gail

 

[footoo]

Try FN+F5.
If doesn't work, maybe something to do with your settings. Try searching for "Excel VBE F5 not working".

The following should not make a difference but try :

Sub grouprows()
With ActiveSheet
    .Cells.ClearOutline
    If .[A8] = "" Then
        .[A8:A858].Rows.Group
    Else
        .Range(.[A7].End(xlDown)(2), .[A858]).Rows.Group
    End If
    .Outline.ShowLevels RowLevels:=1
End With
End Sub