View Single Post
  #6   Report Post  
Old November 27th 20, 08:19 PM posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,848
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