You are here:

Excel/decreasing list VBA



I received your reply and your suggestion works in a new work book using the same cell and sheet numbers given in the directions; however, I already have a huge workbook that I've put a lot of work into and changing my data to the same cells as the directions would cause me hours of work. What part of the VBA code would I need to change if my list was called "Names" and in cells F2:F24 in sheet2 (named "Data Validation") and how would I get the list to not show blank cells (there is a "ignore blank cells" function but it doesn't seem to make a difference whether its checked or not. I tried editing the code myself but it isn't very intuitive and my changes don't work.

Thanks again,


**put his in the worksheet that contains the data validation dropdown.**  The defined range "Names" needs to already exist when this code is first run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strVal As String
Dim strEntry As String
Dim sh As Worksheet, r As Range
Set r = ThisWorkbook.Names("Names").RefersToRange
Set sh = r.Parent
       on Error Resume Next
       strVal = Target.Validation.Formula1
         If Not strVal = vbNullString Then
         strEntry = Target
         Application.EnableEvents = False
         With r
         .Replace What:=strEntry, Replacement:="", _
         LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
         End With
         r.Sort Key1:=r(1), Order1:=xlAscending, Header:=xlNo, _
         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
         r.Resize(r.Rows.Count - 1, 1).Name = "Names"
         End If
       Application.EnableEvents = True
   On Error GoTo 0
End Sub

It worked for me in Windows.  It isn't as dynamic in determining the new extent of names.  It assumes that one name has been eliminated when it runs.   

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.