Delete blank rows or columns in xcel using MACROS

delete blank rows in an multiple xcel sheet using macros

Sub DeleteBlankRows()

    Dim Rw As Long, RwCnt As Long, Rng As Range

    

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    

On Error Goto Exits:

    

    If Selection.Rows.Count > 1 Then

        Set Rng = Selection

    Else

        Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))

    End If

    RwCnt = 0

    For Rw = Rng.Rows.Count To 1 Step -1

        If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then

            Rng.Rows(Rw).EntireRow.Delete

            RwCnt = RwCnt + 1

        End If

    Next Rw

    

Exits:

    Application.ScreenUpdating = True

    Application.Calculation = xlCalculationAutomatic

    

End Sub

---------------------------------------------------------------------------- 

delete blank columns in an multiple xcel sheet using macros including headers


Sub DeleteBlankColumns()

    Dim Col As Long, ColCnt As Long, Rng As Range

    

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    

On Error Goto Exits:

    

    If Selection.Columns.Count > 1 Then

        Set Rng = Selection

    Else

        Set Rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column()))

    End If

    ColCnt = 0

    For Col = Rng.Columns.Count To 1 Step -1

        If Application.WorksheetFunction.CountA(Rng.Columns(Col).EntireColumn) = 0 Then

            Rng.Columns(Col).EntireColumn.Delete

            ColCnt = ColCnt + 1

        End If

    Next Col

    

Exits:

    Application.ScreenUpdating = True

    Application.Calculation = xlCalculationAutomatic

    

End Sub


---------------------------------------------------------------


delete blank rows in an multiple xcel sheet using macros excluding headers

Sub Workbook_Open()

Dim i As Integer

    

    For i = 1 To Worksheets.Count

        Sheets(i).Select

            Dim Col As Long, ColCnt As Long, Rng As Range

           

            Application.ScreenUpdating = False

            Application.Calculation = xlCalculationManual

           

        On Error GoTo Exits:

           

            If Selection.Columns.Count > 1 Then

                Set Rng = Selection

            Else

                Set Rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column()))

            End If

            ColCnt = 0

            For Col = Rng.Columns.Count To 1 Step -1

                If ((Application.WorksheetFunction.CountA(Rng.Columns(Col).EntireColumn) = 0) Or (Application.WorksheetFunction.CountA(Rng.Columns(Col).EntireColumn) = 1)) Then

                    Rng.Columns(Col).EntireColumn.Delete

                    ColCnt = ColCnt + 1

                End If

            Next Col

           

Exits:

            Application.ScreenUpdating = True

            Application.Calculation = xlCalculationAutomatic

    Next i

End Sub

--------------------------------------------------------------------------

How to use:	Copy above code. In Excel press Alt + F11 to enter the VBE. Press Ctrl + R to show the Project Explorer. Right-click desired file on left (in bold). Choose Insert -> Module. Paste code into the right pane. Press Alt + Q to close the VBE. Save workbook before any other changes.
Test the code:	Enter some data in random locations on your spreadsheet Press Alt + F8 to open the macro dialog box. Select DeleteBlankRows Click Run

Triggers in Oracle Forms

Triggers in Oracle Forms

Triggers are blocks of PL/SQL code that are written to perform tasks when a specific event occurs within an application. In effect, an Oracle Forms trigger is an event-handler written in PL/SQL to augment (or occasionally replace) the default processing behavior. Every trigger has a name, and contains one or more PL/SQL statements. A trigger encapsulates PL/SQL code so that it can be associated with an event and executed and maintained as a distinct object.

Block Processing Triggers:

Block processing triggers fire in response to events related to record management in a block.

·                        When-Create-Record Perform an action whenever Oracle Forms attempts to create a new record in a block.

·                        When-Clear-Block Perform an action whenever Oracle Forms flushes the current block; that is, removes all records from the block.

·                        When-Database-Record Perform an action whenever Oracle Forms changes a record’s status to Insert or Update, thus indicating that the record should be processed by the next COMMIT_FORM operation.

Interface Event Triggers:

Interface event triggers fire in response to events that occur in the form interface. Some of these triggers, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others, like When-Window-Activated, can fire in response to both operator input and programmatic control.

·                        When-Button-Pressed Initiate an action when an operator selects a button, either with the mouse or through keyboard selection.

·                        When-Checkbox-Changed Initiate an action when the operator toggles the state of a check box, either with the mouse or through keyboard selection.

·                        When-Image-Activated Initiate an action whenever the operator double-clicks an image item.

·                        When-Image-Pressed Initiate an action whenever an operator clicks on an image item.

·                        When-Radio-Changed Initiate an action when an operator changes the current radio button selected in a radio group item.

·                        When-Window-Activated Initiate an action whenever an operator or the application activates a window.

·                        When-Window-Closed Initiate an action whenever an operator closes a window with the window manager’s Close command.

·                        When-Window-Deactivated Initiate an action whenever a window is deactivated as a result of another window becoming the active window.

Master/Detail Triggers:

Oracle Forms generates master/detail triggers automatically when a master/detail relation is defined between blocks. The default master/detail triggers enforce coordination between records in a detail block and the master record in a master block. Unless developing custom block-coordination schemes, you do not need to define these triggers.

·                        On-Check-Delete-Master Fires when Oracle Forms attempts to delete a record in a block that is a master block in a master/detail relation.

·                        On-Clear-Details Fires when Oracle Forms needs to clear records in a block that is a detail block in a master/detail relation because those records no longer correspond to the current record in the master block.

·                        On-Populate-Details Fires when Oracle Forms needs to fetch records into a block that is the detail block in a master/detail relation so that detail records are synchronized with the current record in the master block.

Message-Handling Triggers:

Oracle Forms automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events.

·                        On-Error Replace a default error message with a custom error message, or to trap and recover from an error.

·                        On-Message To trap and respond to a message; for example, to replace a default message issued by Oracle Forms with a custom message.

Validation Triggers:

Validation triggers fire when Oracle Forms validates data in an item or record. Oracle Forms performs validation checks during navigation that occurs in response to operator input, programmatic control, or default processing, such as a Commit operation.

·                        When-Validate-Item

·                        When-Validate-Record

Navigational Triggers:

Navigational triggers fire in response to navigational events. Navigational triggers can be further sub-divided into two categories: Pre- and Post- triggers, and When-New-Instance triggers. Pre- and Post- Triggers fire as Oracle Forms navigates internally through different levels of the object hierarchy. When-New-Instance-Triggers fire at the end of a navigational sequence that places the input focus on a different item.

·                        Pre-Form Perform an action just before Oracle Forms navigates to the form from “outside” the form, such as at form startup.

·                        Pre-Block Perform an action before Oracle Forms navigates to the block level from the form level.

·                        Pre-Record Perform an action before Oracle Forms navigates to the record level from the block level.

·                        Pre-Text-Item Perform an action before Oracle Forms navigates to a text item from the record level.

·                        Post-Text-Item Manipulate an item when Oracle Forms leaves a text item and navigates to the record level.

·                        Post-Record Manipulate a record when Oracle Forms leaves a record and navigates to the block level.

·                        Post-Block Manipulate the current record when Oracle Forms leaves a block and navigates to the form level.

·                        Post-Form Perform an action before Oracle Forms navigates to “outside” the form, such as when exiting the form.

·                        When-New-Form-Instance Perform an action at form start-up. (Occurs after the Pre-Form trigger fires).

·                        When-New-Block-Instance Perform an action immediately after the input focus moves to an item in a block other than the block that previously had input focus.

·                        When-New-Record-Instance Perform an action immediately after the input focus moves to an item in a different record.

·                        When-New-Item-Instance Perform an action immediately after the input focus moves to a different item. 

Transactional Triggers:

Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source.

·                        On-Delete

·                        On-Insert

·                        On-Update

·                        On-Logon

·                        On-Logout

·                        Post-Database-Commit

·                        Post-Delete

·                        Post-Insert

·                        Post-Update

·                        Pre-Commit

·                        Pre-Delete

·                        Pre-Insert

·                        Pre-Update

Query-Time Triggers:

Query-time triggers fire just before and just after the operator or the application executes a query in a block.

·                        Pre-Query Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.

·                        Post-Query Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. Fires once for each record fetched into the block.