Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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. |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
Your code work perfectly, as well as your
functions . Many many thanks . |
#8
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
What a surprise ...
this bring me both informations I need .. |
#10
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]() 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 | |
|
|
![]() |
||||
Thread | Forum | |||
Excel.Chart size changes after copy-paste 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) |