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

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

"shantanu" skrev i meddelelsen
...
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

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

On Thu, 5 Feb 2009 08:57:46 +0100, "Per Jessen" wrote:

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

"shantanu" skrev i meddelelsen
...
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