Make a new list ignoring blank cells.

[motilulla]

Hello,

I have an original list of numbers with blank cells in the range B2:K2.
I would like a new list in the range B3:K3 ignoring blank cells.
Please help with any formula or VBA

*	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Orignal List With Blank	1		3	4		6	7		9	10
3	New List No Blank	1	3	4	6	7	9	10
4
5

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[footoo]

Try the macro recorder
- copy row 2 to row 3
- select row 3
- go to SpecialCells>Blanks
- delete>shift cells left

 

[Peter_SSs]

.. or more directly

Sub NoBlanks()
  Range("B2:K2").SpecialCells(xlConstants).Copy Destination:=Range("B3")
End Sub

 

[motilulla]

Try the macro recorder
- copy row 2 to row 3
- select row 3
- go to SpecialCells>Blanks
- delete>shift cells left

footoo, thank you for guiding macro recording I tried nearly got it worked but comparing what @Peter made mine recorded is very poor.

Good Luck

Kind Regards,
Moti

 

[motilulla]

.. or more directly

Sub NoBlanks()
  Range("B2:K2").SpecialCells(xlConstants).Copy Destination:=Range("B3")
End Sub

Peter_SSs, just one line code worked fantastic! Thank you for your help

Good Luck

Kind Regards,
Moti

 

[Peter_SSs]

Glad it worked for you. Thanks for the follow-up.