Inserting Cells - A Better Way?

[Katterman]

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)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Area[/TD]
[TD]Order #[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD]4/30/2018[/TD]
[TD]WO12345678[/TD]
[TD]123 Address[/TD]
[TD]xxxx[/TD]
[/TR]
[TR]
[TD]4/30/2018[/TD]
[TD]SAS[/TD]
[TD]WO12345678[/TD]
[TD]123 Address[/TD]
[/TR]
[TR]
[TD]4/30/2018[/TD]
[TD]WO12345678[/TD]
[TD]123 Address[/TD]
[TD]xxxx[/TD]
[/TR]
[TR]
[TD]4/30/2018[/TD]
[TD]SAS[/TD]
[TD]WO12345678[/TD]
[TD]123 Address[/TD]
[/TR]
</tbody>[/TABLE]


Final Result (There are more columns beyond "D" but not needed for the example display here.)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Area[/TD]
[TD]Order #[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD]4/30/2018[/TD]
[TD][/TD]
[TD]WO12345678[/TD]
[TD]123 Address[/TD]
[/TR]
[TR]
[TD]4/30/2018[/TD]
[TD]SAS[/TD]
[TD]WO12345678[/TD]
[TD]123 Address[/TD]
[/TR]
[TR]
[TD]4/30/2018[/TD]
[TD][/TD]
[TD]WO12345678[/TD]
[TD]123 Address[/TD]
[/TR]
[TR]
[TD]4/30/2018[/TD]
[TD]SAS[/TD]
[TD]WO12345678[/TD]
[TD]123 Address[/TD]
[/TR]
</tbody>[/TABLE]

So This is the macro i run

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

 

[pvr928]

Hi Katterman

If the sheet is simply raw text, there isn't any reason that springs to mind that would prevent the macro simply running its course (the act of inserting cells is not instantaneous so over 40000 rows the macro will necessarily take some, but not an inordinate length of, time).

However, if it does seem to be taking much longer than anticipated, there may be another reason. You say "No Calcs are being done on this data sheet" - but are any other worksheets/cells referring to this data? If it is the case (and you are using a lot of formulae like SUMIFS/COUNTIFS/VLOOKUP/INDEX(MATCH) ) this will explain the delay in processing.

One way around this is to simply create a new workbook with this worksheet of data, apply the VBA to it, and then paste/special the reformatted data back into the original worksheet. You may need to re-align your formulae, but this may be a more efficient solve.

Cheers

pvr928

 

[Katterman]

Thanks for the reply pvr928

I adjusted the macro to turn off calcs while running ( then back on when done) and that seemed to speed things up but then i hit the out of memory error.
I then slipped in a "Save" into the macro and all then completed fine.

Seems like i'm good to go. Thanks for the assist. Much Appreciated.

Scott

 

[pvr928]

Hi @Katterman

Thought you mighta had something else going on

Glad to hear it's worked out for you.

The 'out of memory' error sounds like it is worth investigating as I don't believe it directly related to inserting cells. Are you using large strings or arrays, or is your workbook particularly large? Are you being explicit in the objects you are dimensioning? If you don't specify the object (ie [Dim sString] rather than [Dim sString as String], Excel will assign it as a Variant, which takes up more memory. If an Object is no longer required, release it by either:

Set SomeObject = Nothing

or if you are using Arrays:

Erase aArray

There are plenty of hits if you do a search on 'Excel out of memory'.

Cheers

pvr928

 

[Katterman]

Hello pvr928

Sorry for delayed response as i was away last week.

I will research the memory issues as you suggested

Thanks again for your help on this.


Scott