VBA macro for hiding a row

[Former Member]

Hi everyone,

I have a question regarding VBA macros in BEx analyzer. I have few rows in which all values are zeros. I would like to hide such rows. I am trying to write a VBA macro to implement it. Could someone help me with this.

Thanks

R

[Former Member · Accepted solution]

Hi Ram,

I agree with Roberto that there is probably a way to do this entirely within BEx. I would try a Condition.

But ... here is what you've asked for. Note that it is written assuming that there is more than one query in the workbook and that local Query ID for this query is SAPBEXq0001. You might need to adjust these.

This code would replace (or augment) what is currently in the Module named SAPBEX.

Regards, Pete

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

'code to hide any row where all Key Figures are zero

Dim n As Integer, qRow As Integer, RowOffset As Integer

Dim ColOffset As Integer, FirstRow As Integer, FirstCol As Integer

Dim numCells As Integer, LastRow As Integer, LastCol As Integer

Dim i As Integer, KFRange As Range, HideThisRow As Boolean

Dim c As Range

If queryID = "SAPBEXq0001" Then

'locate this query's location in DIM table

Set bexWS = Sheets("SAPBEXqueries")

numQueries = bexWS.Range("A2")

For n = 1 To numQueries

If bexWS.Range("F" & n + 3) = queryID Then

qRow = n + 3

Exit For

End If

Next n

'determine Key Figures offset

RowOffset = bexWS.Range("G" & qRow)

ColOffset = bexWS.Range("H" & qRow)

'define first & last rows & columns for Key Figures

FirstRow = resultArea.Rows(RowOffset).Row

FirstCol = resultArea.Cells(ColOffset).Column

numCells = resultArea.Cells.Count

LastRow = resultArea.Cells(numCells).Row

LastCol = resultArea.Cells(numCells).Column

'search for and hide any row where all Key Figures are zero

For i = FirstRow To LastRow

Set KFRange = Range(Cells(i, FirstCol), Cells(i, LastCol))

HideThisRow = True

For Each c In KFRange.Cells

If IsNumeric(c.Value) Then

If c.Value <> 0 Then HideThisRow = False

End If

Next c

Rows(i).Hidden = HideThisRow

Next i

End If

End Sub

[Former Member]

Hi Roberto and Pete,

Thank you very much for your response. The code works.

Thanks again pete.

Regards

R

[Former Member]

Hi Ram,

are you tried to use the zero suppression function ?

Bye,

Roberto