Delete special cells base on criteria such as blanks or highlights-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Go in advance to proper click on chosen cells and pick the Delete from the right-clicking menu. And then take a look at the Entire row choice in the popping up Delete dialog box, and click on the OK button. Now you will see all the cells containing the sure fee are removed.