How can I delete empty cells in Excel using VBA after a Copy/Paste?-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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