Remove Blank Cell

C

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
 
J

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.
 
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)
 
C

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..
 
K

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').
 
C

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..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top