Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 25th 20, 08:51 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 1
Default 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   Report Post  
Old November 27th 20, 02:03 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 39
Default 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.
  #3   Report Post  
Old November 27th 20, 02:05 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 39
Default 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.
  #4   Report Post  
Old November 27th 20, 05:54 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default 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   Report Post  
Old November 27th 20, 05:59 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default 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   Report Post  
Old November 27th 20, 08:19 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,832
Default 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   Report Post  
Old November 27th 20, 09:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default Copy paste different size problem

Your code work perfectly, as well as your
functions .

Many many thanks .



  #8   Report Post  
Old November 27th 20, 09:24 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,832
Default 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   Report Post  
Old November 27th 20, 10:15 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default Copy paste different size problem

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



  #10   Report Post  
Old November 28th 20, 02:22 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default 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 .


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel.Chart size changes after copy-paste into Word fmamstr Excel Programming 0 November 11th 09 04:44 AM
Object size by copy/paste is different between Office 2003 and 200 fmamstr Excel Programming 2 October 10th 09 02:56 AM
File size - copy paste values only Nikki Excel Discussion (Misc queries) 1 June 22nd 09 04:54 PM
Cell Size Between Worksheets when performing a copy and paste Kara Excel Discussion (Misc queries) 1 October 27th 06 04:08 AM
Excel cut/Paste Problem: Year changes after data is copy and paste Asif Excel Discussion (Misc queries) 2 December 9th 05 06:16 PM


All times are GMT +1. The time now is 08:36 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017