ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Copy paste different size problem (https://www.excelbanter.com/excel-programming/455001-copy-paste-different-size-problem.html)

Dorel Eugen November 25th 20 08:51 PM

Copy paste different size problem
 
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.

NoodNutt[_2_] November 27th 20 02:03 AM

Copy paste different size problem
 
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.

NoodNutt[_2_] November 27th 20 02:05 AM

Copy paste different size problem
 
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.

Xxer Xxes November 27th 20 05:54 PM

Copy paste different size problem
 
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 .

Xxer Xxes November 27th 20 05:59 PM

Copy paste different size problem
 
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 .

Claus Busch November 27th 20 08:19 PM

Copy paste different size problem
 
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

Xxer Xxes November 27th 20 09:05 PM

Copy paste different size problem
 
Your code work perfectly, as well as your
functions .

Many many thanks .




Claus Busch November 27th 20 09:24 PM

Copy paste different size problem
 
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

Xxer Xxes November 27th 20 10:15 PM

Copy paste different size problem
 
What a surprise ...
this bring me both informations I need ..




Xxer Xxes November 28th 20 02:22 AM

Copy paste different size problem
 
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 .


All times are GMT +1. The time now is 04:40 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com