Hi everybody !

I copy in a one column some data , which are in different rows, with empty rows between data. I need code to put that data row by row without empty rows between data.
( I copy this data în another worbook, useing ,,Transpose,, .i select 1500 rows and cant be paste in 265 columns in Excel 2003.) .
That's why i need to comprime data in a few rows

Thank You so much.

Hi ytayt

xl2007 onward can cater for up to 16,384 columns.

https://support.microsoft.com/en-us/...AA=Office_2007

If you need 1500 Columns or more, chances are you will need to upgrade to a later version.

In the event you can't upgrade to a more modern version, it will be difficult to speculate on how to compress 1500 rows into 256 columns without knowing what your 1500 rows of data is.

There are many super-talented programmers here, if it can be done, they're likely to know, but! you need to ready for disappointment given the limitations of your Excel environment.

HTH
Mark.

Hi ytayt

xl2007 onward can cater for up to 16,384 columns.

https://support.microsoft.com/en-us/...AA=Office_2007

If you need 1500 Columns or more, chances are you will need to upgrade to a later version.

In the event you can't upgrade to a more modern version, it will be difficult to speculate on how to compress 1500 rows into 256 columns without knowing what your 1500 rows of data is.

There are many super-talented programmers here, if it can be done, they're likely to know and provide a solution, but! you also need to be prepared for disappointment given the limitations of your Excel environment.

HTH
Mark.

thank you very much for reply

Well , now I am working in excel 2007 , it is so large space here ,
it's an amasing experience . But I realise I will have no way to accomplish
my task without a code to make what I need . So,
i need a code to find all my noncontiguous data in a column , column A ,
and put them in the beginning of this column ( A ) ; So : if
value in cell A100 = 103 , cell A1 must have value = 103 ;
the next cell with data in it can be for eg. A179 , with value = 155 ;
cell A2 must have value =155 ; A3 will must have the value from
the third cell from myRange , A50:A1500 , which range have non
contiguous data ; the cells with no data are empty .

All my data is very simple , only a value , a number from 0 to 500 .
i cant do it with sort , or filter ,and then to copy - paste .

Hi,

Am Fri, 27 Nov 2020 08:59:33 -0800 (PST) schrieb Xxer Xxes:

All my data is very simple , only a value , a number from 0 to 500 .
i cant do it with sort , or filter ,and then to copy - paste .

if you want to do it with VBA then try:

Sub Test()
Dim i As Long, n As Long
Dim varData() As Variant

With ActiveSheet
For i = 101 To 1500
If Len(.Cells(i, "A")) 0 Then
ReDim Preserve varData(n)
varData(n) = .Cells(i, "A")
n = n + 1
End If
Next
.Range("A1").Resize(UBound(varData) + 1) = _
Application.Transpose(varData)
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Your code work perfectly, as well as your
functions .

Many many thanks .

Hi,

Am Fri, 27 Nov 2020 12:05:20 -0800 (PST) schrieb Xxer Xxes:

Your code work perfectly, as well as your
functions .

for your new requirement try:

Sub Test()
Dim varData() As Variant
Dim i As Long, n As Long

With ActiveSheet
n = 1
For i = 31 To 50
If Len(.Cells(1, i)) 0 Then
ReDim Preserve varData(1 To 2, 1 To n)
varData(1, n) = .Cells(1, i)
varData(2, n) = .Cells(1, i).Column
n = n + 1
End If
Next
.Range("A1").Resize(2, n - 1) = varData
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

What a surprise ...
this bring me both informations I need ..

Pe vineri, 27 noiembrie 2020, la 21:19:50 UTC+2, Claus Busch a scris:

Sub Test()
Dim i As Long, n As Long
Dim varData() As Variant

With ActiveSheet
For i = 101 To 1500
If Len(.Cells(i, "A")) 0 Then
ReDim Preserve varData(n)
varData(n) = .Cells(i, "A")
n = n + 1
End If
Next
.Range("A1").Resize(UBound(varData) + 1) = _
Application.Transpose(varData)
End With

Have a plesant night

Well , I try to use this code in a macro ,
to try see which work faster :

With ActiveSheet
For i = 101 To 1500
If Len(.Cells(i, "A")) 0 Then
ReDim Preserve varData(n)
varData(n) = .Cells(i, "A")
n = n + 1
End If
Next
..Range("A1").Resize(UBound(varData) + 1) = _
Application.Transpose(varData)
End With

now , I need to use Column F instead of column A ,
and this part code return me an error 9 ; I tried to change
"A" with "F" , but with the same error 9 return .

Pe sâmbătă, 28 noiembrie 2020, la 03:23:03 UTC+2, Xxer Xxes a scris:

I took a little work with it , and now this code
work in Column F :

Sub Test()
Dim i As Long, n As Long
Dim varData() As Variant

With ActiveSheet
For i = 51 To 1600
If Len(.Cells(i, "F")) 0 Then
ReDim Preserve varData(n)
varData(n) = .Cells(i, "F")
n = n + 1
End If
Next
..Range("F1").Resize(UBound(varData) + 1) = _
Application.Transpose(varData)
End With
End Sub

But I need this code to bring me the number of the adress of
the row which contain data . I.m pretty shure I'll be not bother
you too soon . I dont make wishes , i just have strongly need
this very clever type of code . Many thanks .

Hi,

Am Fri, 27 Nov 2020 18:02:24 -0800 (PST) schrieb Xxer Xxes:

But I need this code to bring me the number of the adress of
the row which contain data . I.m pretty shure I'll be not bother
you too soon . I dont make wishes , i just have strongly need
this very clever type of code . Many thanks .

try:

Sub Test()
Dim i As Long, n As Long
Dim varData() As Variant

With ActiveSheet
n = 1
For i = 51 To 1600
If Len(.Cells(i, "F")) 0 Then
ReDim Preserve varData(1 To 2, 1 To n)
varData(1, n) = .Cells(i, "F")
varData(2, n) = i
n = n + 1
End If
Next
.Range("F1").Resize(UBound(varData, 2), 2) = _
Application.Transpose(varData)
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

ReDim Preserve varData(1 To 2, 1 To n)
varData(1, n) = .Cells(i, "F")
varData(2, n) = i
n = n + 1

It work perfectly !

Thanks , and have a beautifull
weekend Sir !