Remove Blank Cell

[cgx79]

HI,
Anyone know how to shift data up to a blank cell.
Col A is the data n Col B is the result i want. So does anyone know ho
to do it.

I.E

A B C D
-------------------------------------
22 22
33 33
44 44
55 55
66
77
66 88
77
88

i would like it to auto shift up without using marc

 

[JE McGimpsey]

Worksheet functions can only return values to their calling cells. They
can't delete or shift cells - you'll need a macro for that.

In order to write a macro, I think you'd need to explain a bit more
about how column A transforms to column B.

 

[Bob Phillips]

It needs VBA. Here is an example

Dim iLastRow As Long
Dim i As Long

Application.ScreenUpdating = True
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value = "" Then
Cells(i, "A").Delete Shift:=xlUp
End If
Next i
Application.ScreenUpdating = True

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[cgx79]

Col A B C
--------------------------
111 111 111
222 222 222
333 333 333
aaa 444
sss 555
444 444
555 555
333 333

Hi,

Col A is origina record, i only want number so after sortin
using function it will auto hv Col B but for row 4 to 5 it is blan
because of the aaa n sss. I want my final output to be Col C. Blank ar
remove n dupilcate record r not shown...

Can this be done without using marco..

 

[Ken Wright]

Given your example data I can't see how you get Col B through sorting, but
anyway:-

If you have a Col B as you describe, and it is riddled with blanks, then select
Col B and do edit / Go To / Special / Blanks, then hit Edit / Delete / (Then
either 'Delete entire Row if you don't want those rows' OR 'Delete / Shift Cells
Up').

 

[cgx79]

I get my col B thru function... If the col A value is number i will
copy the record to col B, if Col A is not number then it will not cop
to Col B...

After apply the function all this must be done auto, so that i do no
have to update the record everyday..