Delete special cells base on criteria such as blanks or highlights

November 25, 2022

I have some reports that need to be modified by deleting some specific cells such as blank cells or highlights in the background

I have tried to record Macro to delete the special cells. however, the position will be changed regarding the difference of the row numbers. I could not specify the certain position of each cell.

Here is my data

here is what I expect to get

Here is my recorded Macro

`Sub Macro1()

‘Macro1 Macro

Range("B4").Select
Selection.Delete Shift:=xlToLeft
Range("B16").Select
Selection.Delete Shift:=xlUp
Range("B24").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll Down:=12
Range("B30").Select
Selection.Delete Shift:=xlToLeft

End Sub`

>Solution :

You can try the below, change the columns you want to delete

you can change criteria by using xlcelltype XXXX

for xample of xlcelltype

Dim b As Range

For Each b In [B1:B2000].SpecialCells(xlCellTypeBlanks, 1).Offset(0, -1).Areas
    b.Delete Shift:=xlToLeft
Next
    
    
    Cells.Select
    Cells.EntireColumn.Autofit

        
End Sub