Select and delete range

rhino4eva

Board Regular
Joined
Apr 1, 2009
Messages
234
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

ChrisGT7

Board Regular
Joined
Jul 30, 2012
Messages
87
You could try the filter on that column. Something like that:
Code:
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
 
Last edited:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try:-
Code:
Range("A2:G" & lRow).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,659
Office Version
  1. 365
Platform
  1. Windows
In an used cell put
=ISBLANK(A1)
Where A1 is one of the blank cells not getting deleted, what does the formula return?
 

rhino4eva

Board Regular
Joined
Apr 1, 2009
Messages
234

ADVERTISEMENT

the blank cells do report isblank= true
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,659
Office Version
  1. 365
Platform
  1. Windows
In that case I cannot see why MickG's code doesn't work for you.
 

rhino4eva

Board Regular
Joined
Apr 1, 2009
Messages
234
it does work to a degree ... the a column does not delete
it only shuts all the contents of B to G up
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,659
Office Version
  1. 365
Platform
  1. Windows
What happens if you run this,on it's own?
Code:
Sub chk()
Range("A:A").SpecialCells(xlBlanks).Delete xlUp
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,466
Members
409,883
Latest member
asharris90
Top