Tech Jawa > Excel VBScript – Delete Row if Column “X” = “Value”

This is the first entry in my new “Code” category, as I do a LOT of coding for work lately.

So the following script is one of my firsts that I wrote for work.

Sub Delete_Unchanged_Prices()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'  _______        _            _                      '
' |__   __|      | |          | |                     '
'    | | ___  ___| |__        | | __ ___      ____ _  '
'    | |/ _ \/ __|  _ \   _   | |/ _` \ \ /\ / / _` | '
'    | |  __/ (__| | | | | |__| | (_| |\ V  V / (_| | '
'    |_|\___|\___|_| |_|  \____/ \__,_| \_/\_/ \__,_| '
'                                                     '
'           www.techjawa.com/category/code            '
'             www.corey.degrandchamp.com              '
'                                                     '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                     '
' Written  By: Corey DeGrandchamp                     '
' Written  On: December 10, 2008                      '
' Written For: Michigan Drill Corporation             '
' Description: This Excel VBScript will search column '
'              B, and if a cell in B has a null value '
'              then the script deltes the entire row. '
'              This is mainly used for our end of the '
'              year cost changes, but can be modified '
'              to suit your own needs.                '
'                                                     '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                     '
' Edit   Date: June 18, 2009                          '
' About  Edit: Added information.                     '
'                                                     '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Dim Rng As Range, ix As Long
  Set Rng = Intersect(Range("B:B"), ActiveSheet.UsedRange)
  For ix = Rng.Count To 1 Step -1
      If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "" Then
        Rng.Item(ix).EntireRow.Delete
      End If
  Next
done:
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

It basically goes through and checks every row in column B, and if there is a cell with a value of “” (AKA null) then it removes the entire row.

This can be changed to any column/value combination, but this is the specific combination we need at work.

Tags:Excel, Scripts