past without blanks

[PIsabel]

I have a sheet with 30,000 lines and 37 columns.
Each row has a cell that concatenates all the data entered from all the cells in that row.
Then I copy these cells (A1:A30000) and paste them onto another sheet.
For some reason, which I don't know, when I copy and paste values and then sort them, the cells are divided, one part sorted from A1 downwards and the rest sorted from A30,000 upwards.
Can anyone help me with a code that solves my problem?
I need all the data to be together and sorted in ascending order.
Can anyone help me with a code that does this?

 

[etaf]

its probably sorting as text and not numbers
do you paste value - so only the value is in the cell and not a formula

do you have an example we can see

for example
A1
A3
A5
etc all odd numbers in a list

will actually sort like this, so the 1's are together , then the 2's

a1

a11

a13

a15

a17

a19

a21

a23

a25

a27

a29

a3

a31

a33

a35

a37

a39

a41

a43

a45

a47

a5

a7

a9

 

[PIsabel]

this is what I have.

 

[etaf]

cannot see very well in the image or the formulas and copy - but it may be better to share the actual spreadsheet - or put in xl2bb - so we can see the issue

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

other members may know what you want and answer the post anyway - but i would need an example

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

------

 

[PIsabel]

teste.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	ARTIGO							Fabrico							Polimento									Ref.	Cod. barras	Descrição	PC	PVP	Cod. componente	Fornec.
2	Ref.	Cod. barras	Descrição	PC	PVP	Cod. componente	Fornec.	Ref.	Cod. barras	Descrição	PC	PVP	Cod. componente	Fornec.	Ref.	Cod. barras	Descrição	PC	PVP	Cod. componente	Fornec.			82993		Cama lacado preto + estofo azul para colchão 1,60 x 2,00 | | | | riviera honey cor 81 - 2,5	550	1045	82993
3																								82994		Mesa de cabeceira lacado c/ 2 gavetas | | lacado preto mate	150	390	82994
4																								82997		lacar mesa de cabeceira - L 0,60 x 0,50 x 0,38 | amostra de cor ARC - LM15	65	160	82997
5	82993		Cama lacado preto + estofo azul para colchão 1,60 x 2,00 | | | | riviera honey cor 81 - 2,5	550	1045	82993		82993.		Fabrico1 - - Cama lacado preto + estofo azul para colchão 1,60 x 2,00	240		8299301	antonio	82993..		Polimento - - Cama lacado preto + estofo azul para colchão 1,60 x 2,00	165		8299302	custodio			82998		lacar cama L 1,24 x 0,98 | amostra de cor ARC - LM15	145	320	82998
6	82994		Mesa de cabeceira lacado c/ 2 gavetas | | lacado preto mate	150	390	82994		82994.		Fabrico1 - - Mesa de cabeceira lacado c/ 2 gavetas	85		8299401	antonio	82994..		Polimento - lacado preto mate - Mesa de cabeceira lacado c/ 2 gavetas	65		8299402	custodio			82999		Módulo estante lacar L0,90 x 1,32 x 0,38 | amostra de cor ARC - LM15	110	238	82999
7																								829912		lacar móvel TV + gaveta | |	240	494	829912
8
9	82997		lacar mesa de cabeceira - L 0,60 x 0,50 x 0,38 | amostra de cor ARC - LM15	65	160	82997									82997..		Polimento - amostra de cor ARC - LM15 - lacar mesa de cabeceira - L 0,60 x 0,50 x 0,38	65		8299702	custódio
10	82998		lacar cama L 1,24 x 0,98 | amostra de cor ARC - LM15	145	320	82998									82998..		Polimento - amostra de cor ARC - LM15 - lacar cama L 1,24 x 0,98	145		8299802	custódio
11	82999		Módulo estante lacar L0,90 x 1,32 x 0,38 | amostra de cor ARC - LM15	110	238	82999									82999..		Polimento - amostra de cor ARC - LM15 - Módulo estante lacar L0,90 x 1,32 x 0,38	110		8299902	custódio
12
13
14	829912		lacar móvel TV + gaveta | |	240	494	829912		829912.		Fabrico1 - - lacar móvel TV + gaveta	40		82991201	antonio	829912..		Polimento - - lacar móvel TV + gaveta	200		82991202	custodio
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29																								829912.		Fabrico1 - - lacar móvel TV + gaveta	40		82991201	antonio
30																								829912..		Polimento - - lacar móvel TV + gaveta	200		82991202	custodio
31																								82993.		Fabrico1 - - Cama lacado preto + estofo azul para colchão 1,60 x 2,00	240		8299301	antonio
32																								82993..		Polimento - - Cama lacado preto + estofo azul para colchão 1,60 x 2,00	165		8299302	custodio
33																								82994.		Fabrico1 - - Mesa de cabeceira lacado c/ 2 gavetas	85		8299401	antonio
34																								82994..		Polimento - lacado preto mate - Mesa de cabeceira lacado c/ 2 gavetas	65		8299402	custodio
35																								82997..		Polimento - amostra de cor ARC - LM15 - lacar mesa de cabeceira - L 0,60 x 0,50 x 0,38	65		8299702	custódio
36																								82998..		Polimento - amostra de cor ARC - LM15 - lacar cama L 1,24 x 0,98	145		8299802	custódio
37																								82999..		Polimento - amostra de cor ARC - LM15 - Módulo estante lacar L0,90 x 1,32 x 0,38	110		8299902	custódio
Folha1

 

[etaf]

ok thanks for using xl2bb

Each row has a cell that concatenates all the data entered from all the cells in that row.
Then I copy these cells (A1:A30000) and paste them onto another sheet.

how is that done - i dont see any formulas in cell A1

sorry --- really not following what you have copied into the xl2bb and what the issue is
would you mind explaining what you are after based on the xl2bb you have just posted

 

[PIsabel]

I apologize.
I explained everything poorly.
Using the example I sent.
I have to copy:
A3:G30000
H3:N30000
O3:U30000
that are on sheet1 to
X2 which is on sheet2
All data on sheet 1 are the result of formulas and must be pasted as text on sheet 2

I'm using this code.
Comp_artigos = A3:G30000
Comp_fabrico =H3:N30000

    Application.Goto Reference:="Comp_artigos"
    Selection.Copy
    Sheets("Componentes").Select
    Range("X2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
    '
    Sheets("Compostos").Select
    Application.Goto Reference:="comp_fabrico"
    Selection.Copy
    Sheets("Componentes").Select
    Range("X30000").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

 

[etaf]

OH - ok, its VBA , I dont provide answers to VBA questions here , sorry about that
hopefully another member with VBA knowledge will be along and help soon

 

[Alex Blakenburg]

I think your sort is being impacted by 2 things.
1) I suspect the empty cells actually contain an null string ie formulas returning ""
2) your Ref column contains a mix of numbers and strings ie 82993 and 82993.. (trailing full stops)
Can we convert all the numbers to strings in the output ? ie format the output column as Text ?