Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.programming




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. 
#2
Posted to microsoft.public.excel.programming




Copy paste different size problem
Hi ytayt
xl2007 onward can cater for up to 16,384 columns. https://support.microsoft.com/enus/...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 supertalented 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. 
#3
Posted to microsoft.public.excel.programming




Copy paste different size problem
Hi ytayt
xl2007 onward can cater for up to 16,384 columns. https://support.microsoft.com/enus/...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 supertalented 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. 
#4
Posted to microsoft.public.excel.programming




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 . 
#5
Posted to microsoft.public.excel.programming




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 . 
#6
Posted to microsoft.public.excel.programming




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 
#7
Posted to microsoft.public.excel.programming




Copy paste different size problem
Your code work perfectly, as well as your
functions . Many many thanks . 
#8
Posted to microsoft.public.excel.programming




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 
#9
Posted to microsoft.public.excel.programming




Copy paste different size problem
What a surprise ...
this bring me both informations I need .. 
#10
Posted to microsoft.public.excel.programming




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 . 
#11
Posted to microsoft.public.excel.programming




Copy paste different size problem
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 . 
#12
Posted to microsoft.public.excel.programming




Copy paste different size problem
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 
#13
Posted to microsoft.public.excel.programming




Copy paste different size problem
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 ! 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Excel.Chart size changes after copypaste into Word  Excel Programming  
Object size by copy/paste is different between Office 2003 and 200  Excel Programming  
File size  copy paste values only  Excel Discussion (Misc queries)  
Cell Size Between Worksheets when performing a copy and paste  Excel Discussion (Misc queries)  
Excel cut/Paste Problem: Year changes after data is copy and paste  Excel Discussion (Misc queries) 