I am using a SAP Analysis for Office to create my required report, once the report is refreshed I want to generate 4 level of outlines at the end of the Report refresh & also at the click of a macro button , which ever user choose
1) Account Level - Column E
2) GLG Function Level - Column G
3) Job Family Level Column H
4) Band Level - Column J
My report can generate N no of rows depending on how much data system has depending upon the selection made for the report.
Assume that this the output of the Report (which is very small here, but the rows can go up to 10,000 lines as well. I wrote a code as seen in the attached sheet per my requirement but its slowing down the performance and sometimes ending up crashing the excel.
Can anyone please help me with the code , which will be faster and doesn't crashes the excel.
Following is my code which is working but its very slow
***********************************************************************************************************************************************
Sub GroupTheRows()
'**********************************for Account Function**************************************************
Set ReportSH = ThisWorkbook.Sheets("HR Calc Recon")
ReportSH.Activate
With ReportSH.Outline
.AutomaticStyles = False
.SummaryRow = xlSummaryAbove
.SummaryColumn = xlRight
End With
r = 12
StartingRow = 12
Do Until ReportSH.Range("E" & r).Value = ""
ReportSH.Range("E" & r).Activate
If ReportSH.Range("E" & r + 1) = "" Then
ReportSH.Range(Cells(StartingRow, 1), Cells(r, 1)).Rows.Group
GLG_GROUPING (StartingRow)
Exit Do
End If
If ReportSH.Range("E" & r).Value <> ReportSH.Range("E" & r + 1).Value Then
ReportSH.Range(Cells(StartingRow, 1), Cells(r, 1)).Rows.Group
GLG_GROUPING (StartingRow)
StartingRow = r + 2
r = r + 1
End If
r = r + 1
Loop
End Sub
Public Sub GLG_GROUPING(StartingRow As Integer)
'**********************************for GLG Function**************************************************
Set ReportSH = ThisWorkbook.Sheets("HR Calc Recon")
ReportSH.Activate
GLGStartingRow = StartingRow + 1
s = GLGStartingRow
Do Until ReportSH.Range("G" & s).Value = "Total" Or ReportSH.Range("G" & s).Value = ""
ReportSH.Range("G" & s).Activate
If ReportSH.Range("G" & s + 1) = "" Then
ReportSH.Range(Cells(GLGStartingRow, 1), Cells(s, 1)).Rows.Group
JOB_FAMILY (GLGStartingRow)
Exit Do
End If
If (ReportSH.Range("G" & s).Value <> ReportSH.Range("G" & s + 1).Value) Then
ReportSH.Range(Cells(GLGStartingRow, 1), Cells(s, 1)).Rows.Group
JOB_FAMILY (GLGStartingRow)
GLGStartingRow = s + 2
End If
s = s + 1
Loop
End Sub
Public Sub JOB_FAMILY(StartingRow As Integer)
'**********************************for Job Family Function**************************************************
Set ReportSH = ThisWorkbook.Sheets("HR Calc Recon")
ReportSH.Activate
JOBStartingRow = StartingRow + 1
s = JOBStartingRow
Do Until ReportSH.Range("h" & s).Value = "Total" Or ReportSH.Range("h" & s).Value = ""
ReportSH.Range("h" & s).Activate
If ReportSH.Range("h" & s + 1) = "" Then
ReportSH.Range(Cells(JOBStartingRow, 1), Cells(s, 1)).Rows.Group
BAND (JOBStartingRow)
Exit Do
End If
If (ReportSH.Range("h" & s).Value <> ReportSH.Range("h" & s + 1).Value) Then
ReportSH.Range(Cells(JOBStartingRow, 1), Cells(s, 1)).Rows.Group
BAND (JOBStartingRow)
JOBStartingRow = s + 2
End If
s = s + 1
Loop
End Sub
Public Sub BAND(StartingRow As Integer)
'**********************************for Band/Level Column**************************************************
Set ReportSH = ThisWorkbook.Sheets("HR Calc Recon")
ReportSH.Activate
BANDStartingRow = StartingRow + 1
s = BANDStartingRow
Do Until ReportSH.Range("J" & s).Value = "Total" Or ReportSH.Range("J" & s).Value = ""
ReportSH.Range("J" & s).Activate
If ReportSH.Range("J" & s + 1) = "" Then
ReportSH.Range(Cells(BANDStartingRow, 1), Cells(s, 1)).Rows.Group
Exit Do
End If
If (ReportSH.Range("J" & s).Value <> ReportSH.Range("J" & s + 1).Value) Then
ReportSH.Range(Cells(BANDStartingRow, 1), Cells(s, 1)).Rows.Group
BANDStartingRow = s + 2
End If
s = s + 1
Loop
End Sub
******************************************************************************************************************************************************************************
1) Account Level - Column E
2) GLG Function Level - Column G
3) Job Family Level Column H
4) Band Level - Column J
My report can generate N no of rows depending on how much data system has depending upon the selection made for the report.
Assume that this the output of the Report (which is very small here, but the rows can go up to 10,000 lines as well. I wrote a code as seen in the attached sheet per my requirement but its slowing down the performance and sometimes ending up crashing the excel.
Can anyone please help me with the code , which will be faster and doesn't crashes the excel.
Following is my code which is working but its very slow
***********************************************************************************************************************************************
Sub GroupTheRows()
'**********************************for Account Function**************************************************
Set ReportSH = ThisWorkbook.Sheets("HR Calc Recon")
ReportSH.Activate
With ReportSH.Outline
.AutomaticStyles = False
.SummaryRow = xlSummaryAbove
.SummaryColumn = xlRight
End With
r = 12
StartingRow = 12
Do Until ReportSH.Range("E" & r).Value = ""
ReportSH.Range("E" & r).Activate
If ReportSH.Range("E" & r + 1) = "" Then
ReportSH.Range(Cells(StartingRow, 1), Cells(r, 1)).Rows.Group
GLG_GROUPING (StartingRow)
Exit Do
End If
If ReportSH.Range("E" & r).Value <> ReportSH.Range("E" & r + 1).Value Then
ReportSH.Range(Cells(StartingRow, 1), Cells(r, 1)).Rows.Group
GLG_GROUPING (StartingRow)
StartingRow = r + 2
r = r + 1
End If
r = r + 1
Loop
End Sub
Public Sub GLG_GROUPING(StartingRow As Integer)
'**********************************for GLG Function**************************************************
Set ReportSH = ThisWorkbook.Sheets("HR Calc Recon")
ReportSH.Activate
GLGStartingRow = StartingRow + 1
s = GLGStartingRow
Do Until ReportSH.Range("G" & s).Value = "Total" Or ReportSH.Range("G" & s).Value = ""
ReportSH.Range("G" & s).Activate
If ReportSH.Range("G" & s + 1) = "" Then
ReportSH.Range(Cells(GLGStartingRow, 1), Cells(s, 1)).Rows.Group
JOB_FAMILY (GLGStartingRow)
Exit Do
End If
If (ReportSH.Range("G" & s).Value <> ReportSH.Range("G" & s + 1).Value) Then
ReportSH.Range(Cells(GLGStartingRow, 1), Cells(s, 1)).Rows.Group
JOB_FAMILY (GLGStartingRow)
GLGStartingRow = s + 2
End If
s = s + 1
Loop
End Sub
Public Sub JOB_FAMILY(StartingRow As Integer)
'**********************************for Job Family Function**************************************************
Set ReportSH = ThisWorkbook.Sheets("HR Calc Recon")
ReportSH.Activate
JOBStartingRow = StartingRow + 1
s = JOBStartingRow
Do Until ReportSH.Range("h" & s).Value = "Total" Or ReportSH.Range("h" & s).Value = ""
ReportSH.Range("h" & s).Activate
If ReportSH.Range("h" & s + 1) = "" Then
ReportSH.Range(Cells(JOBStartingRow, 1), Cells(s, 1)).Rows.Group
BAND (JOBStartingRow)
Exit Do
End If
If (ReportSH.Range("h" & s).Value <> ReportSH.Range("h" & s + 1).Value) Then
ReportSH.Range(Cells(JOBStartingRow, 1), Cells(s, 1)).Rows.Group
BAND (JOBStartingRow)
JOBStartingRow = s + 2
End If
s = s + 1
Loop
End Sub
Public Sub BAND(StartingRow As Integer)
'**********************************for Band/Level Column**************************************************
Set ReportSH = ThisWorkbook.Sheets("HR Calc Recon")
ReportSH.Activate
BANDStartingRow = StartingRow + 1
s = BANDStartingRow
Do Until ReportSH.Range("J" & s).Value = "Total" Or ReportSH.Range("J" & s).Value = ""
ReportSH.Range("J" & s).Activate
If ReportSH.Range("J" & s + 1) = "" Then
ReportSH.Range(Cells(BANDStartingRow, 1), Cells(s, 1)).Rows.Group
Exit Do
End If
If (ReportSH.Range("J" & s).Value <> ReportSH.Range("J" & s + 1).Value) Then
ReportSH.Range(Cells(BANDStartingRow, 1), Cells(s, 1)).Rows.Group
BANDStartingRow = s + 2
End If
s = s + 1
Loop
End Sub
******************************************************************************************************************************************************************************