0

I want to delete all rows that contain empty cells in a particular range (columns A and B). I tried multiple options, which I found here on SO.

Columns("A:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

and I have also tried:

Lastrow = Range("A:Z").Find("*", , , , xlByRows, xlPrevious).Row

For i = Lastrow To 1 Step -1
    If Application.CountA(Range(Cells(i, 1), Cells(i, 2))) = 0 Then Rows(i).Delete
Next i

However: both options don't find my empty cells. I think the reason must be that they have been pasted (PasteSpecial-Values only) into my Excel sheet. They contain no values, but is it possible there's still some metadata that prevents them from being "found" by the algorithm? Both macros don't find any empty cells and basically do nothing. Thank you for your help.

Martin Graupner
  • 103
  • 1
  • 2
  • 8
  • There are probably spaces. Do a find and replace with nothing then try? – findwindow Jul 26 '22 at 16:12
  • 1
    You can check the LEN of the cells https://stackoverflow.com/questions/14108948/excel-vba-check-if-entry-is-empty-or-not-space – urdearboy Jul 26 '22 at 16:22
  • It is so interesting. There are actually no spaces. However, if I point to one of these cells with the =ISBLANK function it returns FALSE. If I click into the cell and just press enter, it changes to TRUE – Martin Graupner Jul 26 '22 at 16:33
  • Find And Replace should be done by searching for 1 space, replacing it with nothing, and with the "Match entire cell contents" checkbox marked, to avoid incorrectly replacing spaces between words, etc. – ALeXceL Jul 26 '22 at 16:36
  • What happens if you copy both columns, paste special values elsewhere then remove duplicates? – findwindow Jul 26 '22 at 16:39
  • Thanks. If I do a Find and Replace it finds no matching entries, hence there were no spaces. I pasted all these cells from another worksheet with a formula. However, the formula returns no value for some of the cells and since I do a PasteSpecial (values only) I thought it doesn't matter but it seems like there is still some metadata hidden – Martin Graupner Jul 26 '22 at 16:39
  • So, do the Find And Replace searching for =char(160). – ALeXceL Jul 26 '22 at 16:41
  • Find and Replace for char(160) didn't find any results in my sheet – Martin Graupner Jul 26 '22 at 17:27
  • So it's not present. You can test for 'what' is on the cell by typing this in another one: =CODE(TheAddressOfYour'Blank'Cell) – ALeXceL Jul 26 '22 at 17:49
  • @MartinGraupner the link I shared covers the 'ghost data' scenario you outlined. Have you tried the solutions found there? – urdearboy Jul 26 '22 at 17:54
  • @ALeXceL Yes, it's not present. Using =CODE actually returns a #VALUE error here... urdearboy: Thank you - I was not really able to use it earlier, but now using genespos' answer, I tried to integrate your solution (with the vbnullstring) and it also works! However, it seems not really necessary, since the trim command worked anyways here – Martin Graupner Jul 26 '22 at 20:13

2 Answers2

1

Try this

For i = Lastrow To 1 Step -1 
    If trim(cells(i, 1))= ""  or trim(cells(i, 2))= "" then cells(i, 1).entirerow.Delete
Next i
genespos
  • 3,211
  • 6
  • 38
  • 70
  • Thank you very much, this works and finds the cells! I skipped the "or" statement but the rest works absolutely perfect. What does it do exactly? Why is it working and not the other solutions? – Martin Graupner Jul 26 '22 at 16:58
1

If the 'evil' char(160) is present... do this way:

Sub fnReplaceChar160AndDeleteBlankRows()
    Selection.Replace What:="=Char(160)", Replacement:="", LookAt:=xlWhole _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    'now it's possible to delete the 'blank' rows
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete
End Sub
ALeXceL
  • 599
  • 5
  • 11