Select and delete range

rhino4eva

Active Member
Joined
Apr 1, 2009
Messages
258
Office Version
  1. 2010
Platform
  1. Windows
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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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:
Upvote 0
Try:-
Code:
Range("A2:G" & lRow).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
 
Upvote 0
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.!!!
 
Upvote 0
In an used cell put
=ISBLANK(A1)
Where A1 is one of the blank cells not getting deleted, what does the formula return?
 
Upvote 0
In that case I cannot see why MickG's code doesn't work for you.
 
Upvote 0
it does work to a degree ... the a column does not delete
it only shuts all the contents of B to G up
 
Upvote 0
What happens if you run this,on it's own?
Code:
Sub chk()
Range("A:A").SpecialCells(xlBlanks).Delete xlUp
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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
Back
Top