Select and delete range

rhino4eva

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

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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
73,434
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
238

ADVERTISEMENT

the blank cells do report isblank= true
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,434
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
238
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
73,434
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,593
Messages
5,838,257
Members
430,536
Latest member
Manoj Gaidhankar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top