Need help on deleting cells

[shrek]

I have data in a workbook which has blank cells in columns D and E. What im trying to find out is the a macro that will delete the row if the cells are blank in both columns D and E right the way down the workbook.

 

[VoG]

Try this on a copy of your sheet

Sub DelblankDE()
Dim LastRow As Long, i As Long
Application.ScreenUpdating = False
Application.Calculation = xlManual
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
    Cells(i, 1).Value = Trim(WorksheetFunction.Clean(Cells(i, 1).Value))
Next i
Columns("A:A").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
LastRow = Cells(Rows.Count, 4).End(xlUp).Row
For i = LastRow To 1 Step -1
    If IsEmpty(Cells(i, 4).Value) And IsEmpty(Cells(i, 5).Value) Then Rows(i).EntireRow.Delete
Next i
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub

 

[shrek]

Sorry to be a pain vog. tried that and it comes up with #ref in the cells.

 

[VoG]

Sorry to be a pain also but you need to find out what is in the cells in column A that appear to be blank but are not.

 

[shrek]

How would I find that out, because as far as the workbook shows the cells are blank and I dont think there any hidden cells

 

[Richard Schollar]

Point this formula at one of these "blank" cells:

=CODE(LEFT(a1))

and post back what it returns

 

[shrek]

Hi there

Done that and getting code 76 if that means anything.

 

[Richard Schollar]

Did you adjust the reference ie A1 to whatever cell was actually 'blank'? Ascii 76 is a capital L, so I doubt you had this in your balnk cells, unless you have a white font applied.

So, say your blank cell is actually B23 then you need:

=CHAR(LEFT(B23))