VBA Code to remove Data embedded in the "Pick From Drop Down List"

[Steve Belsch]

I have written a code to delete rows if they are blank. See the code below. The code works when the cell is blank. However, the cells appear to be blank. But, in fact there is an embedded set of values that you can see in the "Pick From the Drop Down List" (right click in the cell). This is what it looks like, as an example when I right click on a cell ....

Blank
4701
4702
4703
etc..

But if it appears blank when I look at the cell, how do I get rid of this list, because my code only deletes a row if the row is empty. And in this case it is blank but yet VBA thinks there is a value because of this Drop Down List which includes these values. Make sense?


Any idea how I can get this to be no value when it looks like it is blank?



Code:

Sub Delete_Blank_Rows2()


On Error Resume Next


Range("A1:A100000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete



End Sub

[p45cal]

Go on, I dare you, supply a workbook to demonstrate this, because I can't reproduce this.

[Steve Belsch]

p45cal,

How can I attach a workbook to this thread?

Regards,
Steve

[p45cal]

http://www.vbaexpress.com/forum/faq....b3_attachments

[Steve Belsch]

Please see the attachment. So, as an example please right click on cell A7 and select from drop down list. Even though the cell appears blank there is a list of values that you can select from. How can I make that go away with a VBA code or otherwise?

Thank you.

[p45cal]

you could try something like:

With Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A1:A100000"))
  .Value = .Value
  .SpecialCells(xlCellTypeBlanks).EntireRow.Select    'Delete
End With

but this will convert numbers as text to numbers (messy if they're, say, account numbers beginning with leading zeroes you want to keep).
See more suggestions at https://stackoverflow.com/questions/...ls-arent-blank

[Steve Belsch]

Thank you p45cal. I tried it and it didn't work. It didn't delete the rows that look blank. And I right clicked them and the values are still there. Am I doing this wrong?

[p45cal]

Thank you p45cal. I tried it and it didn't work. It didn't delete the rows that look blank. And I right clicked them and the values are still there. Am I doing this wrong?

1. You do realise I used .Entirerow.Select and not .Delete in my snippet?
2. It worked on the file you attached.

[Paul_Hossler]

A6:A10 are not blank, they contain a 0 length string

A11 and on are really 'blank' = uninitialized


Capture.JPG


I use something like this to 'clear' 0 length strings and make them truly empty

Option Explicit


Sub phh()


    With ActiveSheet.Columns(1)
        'clear any empty, but text i.e. 0 length strings
        Call .Replace(vbNullString, "###ZZZ###", LookAt:=xlWhole)
        Call .Replace("###ZZZ###", vbNullString, LookAt:=xlWhole)
        
        'clear the settings
        .Find What:=vbNullString, After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
               SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False
        .Replace What:=vbNullString, Replacement:=vbNullString, ReplaceFormat:=False


        On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        On Error GoTo 0
    
        End With


End Sub

[Steve Belsch]

p45cal,

This worked when I put .Delete like you suggested. Thank you!!!

Solved.