Katterman
Board Regular
- Joined
- May 15, 2014
- Messages
- 103
- Office Version
- 365
- Platform
- Windows
- Mobile
- Web
Hello All
I have a small macro that i use to insert a cell if the character count is greater than 3 in a specific column of that row.
this is due to before that Insert occurs, a text To column (delimited by semi colon and coma) macro runs but since the source data is a bit flawed (and out of my control there) that often a piece of "Area" data is missing so the end result the data is a bit out alignment per row.
Sample data below. Rows 3 and 5 are correct. Rows 2 & 4 need to shift 1 to the right by Column B to realign data in Column C and beyond. After the shift Cells B2 & B4 can remain Blank. ( Note, The data is just Text and No Calcs are being done on this data sheet)
<tbody>
</tbody>
Final Result (There are more columns beyond "D" but not needed for the example display here.)
<tbody>
</tbody>
So This is the macro i run
The problem is, that the data could be at least 40000 rows and aside for taking some time, Excel will run out of memory (Excel 2016 32bit and also another machine Excel 2016 64bit).
Right now to get around the macro failure, after a few minutes i will Halt the macro, save the sheet and restart the macro. Sometimes a couple times to save and restart depending if more that 40000 etc. That will end up completing the task but kind of defeats the Automation purpose of the macro.
Open to either better process or code if someone can help.
Thanks so much for all who read all this and also those who offer assistance.
Scott
I have a small macro that i use to insert a cell if the character count is greater than 3 in a specific column of that row.
this is due to before that Insert occurs, a text To column (delimited by semi colon and coma) macro runs but since the source data is a bit flawed (and out of my control there) that often a piece of "Area" data is missing so the end result the data is a bit out alignment per row.
Sample data below. Rows 3 and 5 are correct. Rows 2 & 4 need to shift 1 to the right by Column B to realign data in Column C and beyond. After the shift Cells B2 & B4 can remain Blank. ( Note, The data is just Text and No Calcs are being done on this data sheet)
Date | Area | Order # | Address |
4/30/2018 | WO12345678 | 123 Address | xxxx |
4/30/2018 | SAS | WO12345678 | 123 Address |
4/30/2018 | WO12345678 | 123 Address | xxxx |
4/30/2018 | SAS | WO12345678 | 123 Address |
<tbody>
</tbody>
Final Result (There are more columns beyond "D" but not needed for the example display here.)
Date | Area | Order # | Address |
4/30/2018 | WO12345678 | 123 Address | |
4/30/2018 | SAS | WO12345678 | 123 Address |
4/30/2018 | WO12345678 | 123 Address | |
4/30/2018 | SAS | WO12345678 | 123 Address |
<tbody>
</tbody>
So This is the macro i run
Code:
Sub Insert_Cells()
Dim r As Long
Application.ScreenUpdating = False
r = Cells(Rows.Count, 1).End(xlUp).Row
For r = r To 1 Step -1
If Len(Cells(r, 2)) > 3 Then Cells(r, 2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Next r
Application.ScreenUpdating = True
End Sub
The problem is, that the data could be at least 40000 rows and aside for taking some time, Excel will run out of memory (Excel 2016 32bit and also another machine Excel 2016 64bit).
Right now to get around the macro failure, after a few minutes i will Halt the macro, save the sheet and restart the macro. Sometimes a couple times to save and restart depending if more that 40000 etc. That will end up completing the task but kind of defeats the Automation purpose of the macro.
Open to either better process or code if someone can help.
Thanks so much for all who read all this and also those who offer assistance.
Scott