Excel Macro VBA code to delete rows in excel sheet meeting multiple conditions

Macro VBA code to delete rows in excel sheet meeting multiple conditions

1. Add a button to a new sheet

2. Right click on button and Click on Assign Macro

3. Put below code in the Macro. This code will delete rows from Sheet “Employee” in Current Workbook with Column L having value “1” and Column H having value “Santosh”. Loop from Last row to First row

Sub Button1_Click()

Dim Firstrow As Long
 Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim deleteFlag As Boolean
On Error Resume Next

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
Set gd = ThisWorkbook
Set ws = gd.Sheets(“Employee”)
    ‘Using Sheet(“Employee”) in Current Workbook
    With ws

        ‘We select the sheet so we can change the window view

        ‘If you are in Page Break Preview Or Page Layout view go
        ‘back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        ‘Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        ‘Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        ‘We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1
           deleteFlag = False
            ‘We check the values in the A column in this example
            With .Cells(Lrow, “L”)

                If Not IsError(.Value) Then

                    If .Value = “1” Then deleteFlag = True
                    ‘This will set delete flag for each row with the Value “1”
                    ‘in Column L.

                End If

            End With
            With .Cells(Lrow, “H”)

                If Not IsError(.Value) Then

                    If .Value = “Santosh” Then deleteFlag = True
                    ‘This will set delete flag for each row with the Value “Santosh”
                    ‘in Column H, case sensitive.

                End If

            End With
            If deleteFlag = True Then .Rows(Lrow).EntireRow.Delete

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With


MsgBox (“Done”)

End Sub

Excel Data Validation

Use data validation in Excel to make sure that users enter correct values into a cell. Data validation option is found under Data Menu in Excel. This feature restricts user from entering incorrect values in cell.

For example , in Student Workbook , Marks is whole number greater than 0 and less than or equal to 100. 
Select Marks cell(s) and click on Data -> Data Validation
Data Validation Dialog box shows number of validation criteria options ( Any Value , Whole Number , Decimal , List , Date , Time , Text length , Custom )
For above marks data validation, we will select Whole number and specify minimum value as 0 maximum value as 100.
To configure Input message to be shown on selected cell , Navigate to Input Message tab and enter text in Title ( Total Marks = 100 ) and Input Message textbox ( Marks can be between 0 to 100 ).
To configure Error Alert click on Error Alert tab , Select Style ( Stop , Warning or Information ) and enter Title and Error Message.
Since we do not want user to enter number less than 0 and greater than 100 , so select Style as Stop and Enter Title : Enter Correct Marks and Error Message : Marks can be between 0 to 100
Then click on OK button to apply data validation on a particular cell(s)
When Marks cell is selected , it shows below message based on Data Validation setting done :
Enter incorrect number i.e. number below 0 or number above 100 , it will show below error message

Use Consolidate in Excel with an example

Use Consolidate in Excel with an example

Consolidate option under Data menu is used to perform summary calculations on values in corresponding cells in various source ranges. Consolidate command is used to find the count, sum, product, minimum, maximum, variance, standard deviation, or average of the values in a group of ranges.

Example :

Below figure shows workbook containing ranges you want to consolidate :

Range 1 shows Quarter wise Sales data for Products in Shop 1 
Range 2 shows Quarter wise Sales data for Products in Shop 2

We need to consolidate Quarter wise sales data for products from both shops

1. Click on Data -> Consolidate
2. Consolidate Dialog box shows below Consolidate function 

3. Select Sum in Consolidate Function

4. Select the first range in Worksheet in Reference

5. Click on Add button to add to All references :

6. Select the second range in Worksheet in Reference and click on Add Button.

7. Check Top row and Left column checkbox and Click in OK button in Consolidate dialog to consolidate data

Enable Fill Handle in Microsoft Excel

Enable Fill Handle in Microsoft Excel

  1. Open Microsoft Excel and then click on File -> Options.
  2. Click Advanced, and then under Editing options,  select the Enable Fill handle and cell drag-and-drop check box show the fill handle.
  3. To avoid replacing existing data when you drag the fill handle, make sure that the Alert before overwriting cells check box is selected. If you don’t want to see a message about overwriting nonblank cells, you can clear this check box.

Use the fill handle to fill data

To quickly fill in several types of data series, you can select cells and drag the fill handle Fill handle

Create Custom List in Excel

Step 1 : Navigate to Excel Options -> Click on Advanced 

Step 2 : Navigate to General Section -> Click to Edit Custom Lists
 Step 3 : Custom Lists Dialog Box opens with existing Custom lists

Step 4 : Enter Custom list values in List Entries text and Click on Add and Ok to add new Custom List


Step 5 : You can also add Custom List by Importing values from excel cell values


Enable or Disable fill handle in Excel

Enable or Disable fill handle in Excel
One can turn this option on or off by following below steps:
  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Advanced category, under Editing options, select or clear the Enable fill handle and cell drag-and-dropcheck box to turn drag-and-drop editing on or off.