Look at what i have done manually
Shift data to next row , should not exceed column G
Data A1:M20
See sample expected to A22:G44
Macro to work at 44,444 rows
Note : Push data to next line starting column C if data extends column G
Look at what i have done manually
Shift data to next row , should not exceed column G
Data A1:M20
See sample expected to A22:G44
Macro to work at 44,444 rows
Note : Push data to next line starting column C if data extends column G
Please Login or Register to view this content.
Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
Try:
PHP Code:
Option Explicit
Sub test()
Dim lr&, i&, j&, r&, k&, c&, t&, rng, arr(1 To 10000, 1 To 7)
rng = Range("A1").CurrentRegion.Value
For i = 1 To UBound(rng)
r = t + 1
arr(r, 1) = rng(i, 1): arr(r, 2) = rng(i, 2)
For j = 3 To UBound(rng, 2)
If rng(i, j) <> "" Then
t = r + Int((j - 3) / 5)
c = ((j - 3) Mod 5) + 3
arr(t, c) = rng(i, j)
End If
Next
Next
Range("A1:ZZ100000").ClearContents
Range("A1").Resize(r, 7).Value = arr
End Sub
Quang PT
Thank u sintek and bebo21999
Will ask for an add on the macro , at column H i need to to count na alpha
See manually plugged results
I don't understand how you got those results...Why is H14, H16, H17 blank?
If this below is actual result required...
Untitled.png
Then this amendment will work...
Please Login or Register to view this content.
Last edited by Sintek; 09-13-2022 at 05:18 AM.
for AEINRST it is 5 + 5 + 1 that is 11
It should plug 11 at row 15 and leave blank at row 16 and 17
Last edited by makinmomb; 09-13-2022 at 05:32 AM. Reason: detailed
Please Login or Register to view this content.
It is blank as AEINSTR is a set of 11 words 5 each on two rows and 1 additional
Jindon yours too does not do 11 for h16
No idea why you want it 9 instead 11, so I'm out.
Jindon
These are 11
ANESTRI ANTSIER NASTIER RATINES RESIANT
RETAINS RETINAS RETSINA STAINER STARNIE
STEARIN
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by jindon; 09-13-2022 at 06:26 AM. Reason: Fixed a bug
Run Time Error 9 on SIntek Macro on real data which is 44,000 rows
Jindon your macro is not working on 44,ooo rows and pours on results of test data given
Last edited by makinmomb; 09-13-2022 at 07:11 AM. Reason: 44000
Change red + to *
Please Login or Register to view this content.
Sintex please paste code again so we can close thread , getting compile error
Tried 80000 rows with 30 cols data and no error.
Please Login or Register to view this content.
Find attached file with added details for upto 5oo rows to see if it works
DO NOT MIX MY CODE WITH OTHERS.
Please Login or Register to view this content.
Not working here are the results of the macro your jindon
Find attached
Have you tried mine? with that small change of + to *
Really hoepless...
Thank u Jindon bionic it pushed all that mass data at 1 second ,crazy
What is the meaning of binary macro , i have always saved files as Macro enabled , this binary thingi is my first time
Sintek yours working as quick as Jindon , button Jindon is faster by another 3 seconds as his got that run button
I have collected both versions including that of Sintek , it is as good as that of Jindon
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks