Deleting blank Cells and Cells containing Special Characters as # after Applying Trim Function in an entire column

Question

i hope u'r fine.

I have an ID Column containing some Blank Cells with spaces inside and other cells with information some of which with space & others with #

the aims is to apply TRIM function into the ID column and delete Blank Cells and Special Characters. Rm : if I try to make deleting of the Blank cells before Applying Trim function VBA will not recognize them as empty cells. but even after Applying Trim function Still not recognize them as empty cells. Same Story with # So I tried to make TRIM function and then copy Paste Column with only a value to delete TRIM Function in case. but same probleme. Tanks for your help

Here is the code

    Sub Trim()

 Dim Worksht As Worksheet
 Dim TargetCell As Range
 Dim DurtyRows As Range


 Set Worksht = ActiveSheet

 Set TargetCell = ActiveSheet.UsedRAnge.Find(What:="ID", LookAt:=xlWhole)

 Range(TargetCell.Offset(1, 0), TargetCell.Offset(1, 0).End(xlDown)).Copy
 TargetCell.Offset(1, 1).Select
 'To Apply TRIM Function in an copied column 
 ActiveSheet.Paste
 Application.CutCopyMode = False
 TargetCell.Offset(1, 1).Select

 ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"

 Selection.AutoFill Destination:=Range(TargetCell.Offset(1, 1), 
 TargetCell.Offset(1, 1).End(xlDown))

 'Replacing the initial Column with  TRIM Function Result Column

 Range(TargetCell.Offset(1, 1), TargetCell.Offset(1, 1).End(xlDown)).Copy

 TargetCell.Offset(1, 0).Select

 Selection.PasteSpecial Paste:=xlPasteValues

 Application.CutCopyMode = False

 Range(TargetCell.Offset(1, 1), TargetCell.Offset(1, 1).End(xlDown)).Delete

 Range(TargetCell.Offset(1, 0), TargetCell.Offset(1, 0).End(xlDown)).Select

 ''''the error message for.SpecialCells (xlCellTypeBlanks) " no corresponding  
 ''cell

 Set DurtyRows = ActiveSheet.Range(TargetCell.Offset(1, 0), TargetCell.Offset(1, 0).End(xlDown)).SpecialCells(xlCellTypeBlanks)

 DurtyRows.Delete

 End Sub

related: https://stackoverflow.com/questions/32105314/delete-row-if-cell-in-changing-column-is-blank?rq=1 — , Jul 08 '18 at 16:38

score 1 · Accepted Answer · answered Jul 08 '18 at 15:30

1

Try using Text-to-Columns to 'trim' the cells into truly blank cells after replacing #'s with a null string.

with worksheets("sheet1")
    with .range(.cells(2, "B"), .cells(.rows.count, "B").end(xlup))
        .replace what:=chr(35), replacement:=vbnullstring
        .texttocolumns  Destination:=.cells(1), _
                        DataType:=xlFixedWidth,  FieldInfo:=Array(0, 1)
        .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    end with
end with

answered Jul 08 '18 at 15:30

1

It works except for cells which contain # with space like this [ # ] so I changed the code like this and it woks – DataScientist Jul 08 '18 at 16:55
Thnks for your help – DataScientist Jul 08 '18 at 16:59

score 1 · Answer 2 · answered Jul 08 '18 at 17:02

1

With Worksheets("trim")
    With .Range(TargetCell.Offset(1, 0), TargetCell.Offset(1, 0).End(xlDown))
    .TextToColumns Destination:=.Cells(1), _
                        DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
        .Replace what:=Chr(35), replacement:=vbNullString
        .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    End With
End With

answered Jul 08 '18 at 17:02

DataScientist

67
1
1
7

Good job reordering the logic flow! – Jul 08 '18 at 19:28

Deleting blank Cells and Cells containing Special Characters as # after Applying Trim Function in an entire column

2 Answers2