Delete Blank Excel Rows

[shantanu]

Hello All

I have a problem, i have a worksheet that contains 500 rows of data.
Now between these 500 rows there are multiple blank cells, that are
not evenly distributed in the columns. Now for a perticular column i
want to remove the blank cell . Kindly provide me with some example. I
am provided some example below.


A B C
1 1 1
2 1
------------------------------------
------------------------------------
2 1 2
2 2'
3 3
---------------------------------------
---------------------------------------
----------------------------------------

i only want to remove blank cells from column B

Thanks in advance
Shantanu

 

[Per Jessen]

Hi

Assuming you want to delete entire row when a cell in column B is blank,
this should do it:

Sub RemoveBlankCells()
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, 2).Value = "" Then
Rows(r).Delete
End If
Next
End Sub

Regards,
Per

 

[Gord Dibben]

Or if you want to delete empty cells in just column B

Sub RemoveBlankCells()
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, 2).Value = "" Then
Cells(r, 2).Delete Shift:=xlUp
End If
Next
End Sub


Gord Dibben MS Excel MVP