how to clear a worksheet from vba in excel 2016

#How to clear a worksheet from vba in excel 2016 code#

Hope this takes a bit less than a couple years to get through! Set dEntries = Nothing ' These do a little cleanup Me('ComboBoxLocation').List = dEntries.Keys ' Keys is a 1-dimensional array of the KeyValues MsgBox 'Debug> Entries' & vbCrLf & Join(dEntries.Keys, vbCrLf) ' Comment this line out - helpful for debug If rngThisCell.Value '' Then dEntries(rngThisCell.Value) = 1 ' the 1 is just a placeholder Set rngMyItems = rngMyItems.Resize(wksTarget.Cells(Rows.Count, 4).End(xlUp).Row - 1, 1)įor Each rngThisCell In rngMyItems.Cells() ' > Using Column 4 because that's Column 'D' -> Where your selection data is ' > For Rows, '-1' is because we are Resizing and starting Row 2. ' then the range with your selections is referenced in the variable rngMyItems: ' Resize the range from one cell to all of those used in Column D, Set wksTarget = ('Data')ĭEntries.CompareMode = TextCompare ' Case InSensitive! so 'Blue = blue' Once you have that, you can create the Scripting Dictionary as shown in the re-write below: Private Sub prComboBoxFill3() This little gem contains the Scripting.Dictionary among other helpful classes. To do this, click on Tools/References in the VB editor's menu and put a checkbox in the Microsoft Scripting Runtime as shown:

#How to clear a worksheet from vba in excel 2016 code#

Have code that works, add a reference to Microsoft Scripting Runtime. I'll add a re-write of Deepak's code so you might see a little more of what's going on.To get the intellisense working and confirm that indeed you Here's a little more info regarding Scripting.Dictionaries so you can get the 'Intellisense' view of things.