Skip to Content
Technical Articles
Author's profile photo Sruti P

To overcome two Crosstab Overlap in Analysis Office.


Most of the time in Analysis Office we use single query and multiple queries in multiple sheet. When two queries are used in same sheet of Analysis Office, one after another there may be a risk of overlapping of the crosstab if the data increases and we get a warning message in Analysis Office. Causing below error:


Overlap warning message


To overcome it we can write VB macro so the data can be presented without any issue.


The Crosstab1 can start at A5 cell whereas the Crosstab2 can start at A10000 as per the code the blank rows between this 2 Crosstab will be in hide and will get increased as the data increases in Crosstab1.


VB Code:

This Workbook:

Public Sub Workbook_SAP_Initialize()

‘ register callbacks

Call Application.Run(“SAPExectueCommand”, “RegisterCallback”, “AfterRedisplay”, “CallbackAfterRedisplay”)

End Sub


Create a Module and write below code:

Public Sub Callback_AfterRedisplay()

Dim intGap As Integer

Dim intGap2 As Integer

Dim ws1 As Worksheet

Dim ws2 As Worksheet

Set ws1 = ThisWorkbook.Worksheets(“Sheet1”)

Set ws2 = ThisWorkbook.Worksheets(“Sheet2”)

If ws1 Is ActiveSheet Then

intGap = ThisWorkbook.Worksheets(“Sheet1″)

Worksheets(“Sheet1”).Application.ScreenUpdating = False


Selection.EntireRow.Hidden = False

Rows(intGap & “:9999″).Select

Selection.EntireRow.Hidden = True


Worksheets(“Sheet1”).Application.ScreenUpdating = True


intGap2 = ThisWorkbook.Worksheets(“Sheet2”)

Worksheets(“Sheet2”).Application.ScreenUpdating = False


Selection.EntireRow.Hidden = False

Rows(intGap2 & “:9999”).Select

Selection.EntireRow.Hidden = True


Worksheets(“Sheet2”).Application.ScreenUpdating = True

End If

End Sub



To overcome the overlap of 2 different queries in the same sheet we can use VB macro code, this code is not only for one sheet if the user is going on for 2 or 3 sheet, they can use ELSE IF loop to proceed the code and attain the result.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo F Antonio
      F Antonio

      Hi, Sruti.

      txs for sharing.

      CallbackAfterRedisplay OR Callback_AfterRedisplay   ?

      Author's profile photo Sruti Perumal
      Sruti Perumal

      Hi Antonio,


      Apologies for the late reply it is Callback_AfterRedisplay()