Hi
Originally Posted by
ayazgreat
… 5.5 lacs rows
…Lacs?? I do not recognise this word. Internet tells me that possibly lacs or lakh is Indian and/ or Pakistani for 100000? Is that correct?
So 5.5 lacs rows is like 550000 – a bit over half a million ?
It is interesting feedback for me to know that the macro works with such large row numbers. ( I have no practical experience with these things: Currently my Computer forum work is just a part time Hobby )
I was surprised that my code works with such high numbers of rows.
Originally Posted by
ayazgreat
…these code are taking a long time
I cannot really make any useful comment on the speed performance. I don’t have enough practical experience using my coding with real large data.
Just for learning purposes and info:
The coding I did for you uses the Clipboard in a very unusual and unconventional way. I have been doing this for a few months. Maybe someone somewhere did it before, but so far I have never seen anyone else do anything like it. It is possibly it is new stuff that nobody ever did before. Or maybe not. I am not sure. But I can certainly say that it is not usual or typical to do coding like this.
Basically what the coding does in Sub Attempt1() is
the following: To explain / summarise Sub Attempt1()
(Unconventional use of the clipboard)
It basically puts all the data in a VBA array. It works on that data from the array within VBA. (This is typical practice and is generally regarded as more efficient that working with the data row by row, from the worksheet)
It loops once through the data. – That is all normal and conventional.
It builds up a single text string for the output, which is put in the clipboard and then pasted out. - This is the unusual stuff
Here is a couple of more codings that you might like to try
Sub Conventional()
This has one major difference to Sub Attempt1()
It does not build up a long string to put in the clipboard and paste out. It does not use the clipboard.
Instead it pastes out each result line as it goes along in the loop.
This coding is much more typical and usual.
It is what most people would do
Code:
Sub Conventional() ' https://excelfox.com/forum/showthread.php/2836-Data-Copy?p=19600#post19600
On Error GoTo Bed
Let Application.ScreenUpdating = False
Dim arrIn() As Variant: Let arrIn() = Worksheets("Data").Range("A2:D" & Worksheets("Data").Range("C" & Rows.Count & "").End(xlUp).Row + 1 & "").Value2 '
Dim wsRes As Worksheet: Set wsRes = ThisWorkbook.Worksheets.Item("Required Result")
' First info for Start Date
Dim Cnt As Long: Let Cnt = 1
Dim StrDt As Double: Let StrDt = arrIn(Cnt, 3)
Do While arrIn(Cnt, 1) <> "" ' This will stop us when we go above the data range
Do While (Int(arrIn(Cnt + 1, 3)) = Int(arrIn(Cnt, 3)) Or Int(arrIn(Cnt + 1, 3)) = Int(arrIn(Cnt, 3)) + 1) And arrIn(Cnt, 1) = arrIn(Cnt + 1, 1) ' This effectively stops us when the criteria for the data in multiple rows belonging in a single row in Results no longer is met ------------------------
Let Cnt = Cnt + 1
Loop ' --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Let wsRes.Range("A" & wsRes.Range("A" & Rows.Count & "").End(xlUp).Row + 1 & ":D" & wsRes.Range("A" & Rows.Count & "").End(xlUp).Row + 1 & "").Value = Array(arrIn(Cnt, 1), arrIn(Cnt, 2), StrDt, (arrIn(Cnt, 3)))
' Its time now to move on to the next data set required for Results
Let Cnt = Cnt + 1
Let StrDt = arrIn(Cnt, 3) ' Next info for Start Date
Loop
Bed:
Let Application.ScreenUpdating = True
End Sub
Sub Attempt1_Simplified()
This is the same basic coding as Sub Attempt1()
It is just simplified slightly. ( I have also omitted the extra stuff to help always get correct date format )
This sort of simplification you could do, or may have already considered, yourself.
( Initially when I give coding help in forums, I attempt to help in learning and so have some extra bits to aid clarity and learning.
Code:
Sub Attempt1_Simplified() ' https://excelfox.com/forum/showthread.php/2836-Data-Copy?p=19600#post19600
Dim arrIn() As Variant: Let arrIn() = Worksheets("Data").Range("A2:D" & Worksheets("Data").Range("C" & Rows.Count & "").End(xlUp).Row + 1 & "").Value2 '
' First info for Start Date
Dim Cnt As Long: Let Cnt = 1
Dim StrDt As Double: Let StrDt = arrIn(Cnt, 3)
Do While arrIn(Cnt, 1) <> "" ' This will stop us when we go above the data range
Do While (Int(arrIn(Cnt + 1, 3)) = Int(arrIn(Cnt, 3)) Or Int(arrIn(Cnt + 1, 3)) = Int(arrIn(Cnt, 3)) + 1) And arrIn(Cnt, 1) = arrIn(Cnt + 1, 1) ' This effectively stops us when the criteria for the data in multiple rows belonging in a single row in Results no longer is met ------------------------
Let Cnt = Cnt + 1
Loop ' --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Dim strClp As String
Let strClp = strClp & arrIn(Cnt, 1) & vbTab & arrIn(Cnt, 2) & vbTab & StrDt & vbTab & (arrIn(Cnt, 3)) & vbCr & vbLf ' A single line in results in a format that the clipboard holds for an Excel row
' Its time now to move on to the next data set required for Results
Let Cnt = Cnt + 1
Let StrDt = arrIn(Cnt, 3) ' Next info for Start Date
Loop
' put full Results string of data into clipboard, and paste out
With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") ' http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/ get text data from the clipboard or from the interpretaion representation of it - the Office thing that might be called the Office clipboard http://www.eileenslounge.com/posting.php?mode=edit&f=27&p=301028
.SetText strClp
.PutInClipboard
End With
Worksheets("Required Result").Paste Destination:=Worksheets("Required Result").Range("A2")
End Sub
Alan
Reply With Quote
Bookmarks