Skip to Content
author's profile photo Former Member
Former Member

VBA macro for hiding a row

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 01, 2004 at 02:50 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 01, 2004 at 08:32 AM

    Hi Ram,

    are you tried to use the zero suppression function ?

    Bye,

    Roberto

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 01, 2004 at 07:21 PM

    Hi Roberto and Pete,

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

    Thanks again pete.

    Regards

    R

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.