Select and delete range

[rhino4eva]

Sub adam()

Sheets("Tabulate").UsedRange
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("c2", "d" & LRow).SpecialCells(xlCellTypeBlanks).Select

End Sub

I use the above code to select blank cells in columns C & D
I am having trouble achieving the next step

I want to delete the row that the selected rows are found in
I cant use entirerow.delete as it interferes with a pivot table yo the right
I just want to delete and shift up the range column A to G that have blank cells all the way down to the LRow

 

[ChrisGT7]

You could try the filter on that column. Something like that:

Sub Macro1()
    With ActiveSheet.Range("C:D")
        .AutoFilter Field:=1, Criteria1:="="
        .AutoFilter Field:=2, Criteria2:="="
        Rows("2:" & Rows.Count).SpecialCells(xlCellTypeVisible).Delete
        .AutoFilter
    End With
End Sub

 

[MickG]

Try:-

Range("A2:G" & lRow).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp

 

[rhino4eva]

Try:-

Range("A2:G" & lRow).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp

nearly but not quite
your code doesn't take out the entries in "A" column

 

[MickG]

I can't duplicate that, it works on "A to G" for me.
Try the code with data on a new sheet, see if that works.!!!

 

[Fluff]

In an used cell put
=ISBLANK(A1)
Where A1 is one of the blank cells not getting deleted, what does the formula return?

 

[rhino4eva]

the blank cells do report isblank= true

 

[Fluff]

In that case I cannot see why MickG's code doesn't work for you.

 

[rhino4eva]

it does work to a degree ... the a column does not delete
it only shuts all the contents of B to G up

 

[Fluff]

What happens if you run this,on it's own?

Sub chk()
Range("A:A").SpecialCells(xlBlanks).Delete xlUp
End Sub