Syndication

Sponsors


Administration


Posted on June 18, 2009 at 12:18 pm - by Corey DeGrandchamp

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:,

Leave a Reply

Please log in with your Facebook account -OR- fill in the forms below.

About This Blog

Tech Jawa is a blog founded by Corey DeGrandchamp, and loosely based on technology, tutorials, and video games. Please feel free to browse the site using the "Category" list on the left sidebar, and be sure to check out our live stream!

Connect

Sponsors