Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop too slow deleteing xltoleft
Good afternoon,
I am re-creating a SAP profit centre hierarchy in excel. Previous macros that i use will put data in columns A to column G. This will give me a spreadsheet that looks like this: Assume y's and x's are 10 digit number/letters A B C D E F G yyyyy xxxxx xxxxx xxxxx xxxxx xxxxx yyyyy xxxxx xxxxx xxxxx xxxxx yyyyy xxxxx yyyyy xxxxx xxxxx yyyyy xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx yyyyy xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx yyyyy xxxxx xxxxx xxxxx The macro, attached below, I have written checks to see if there is anything in column A and if nothing in col B then delete left till column B has a value. Then move onto the next row. The problem is that it is too slow, over 9 mins, for it too process anything between 4000 and 25000 rows. Can you please help and include comments so that I can learn. Ta, Marc Sub parent_alignment() Application.StatusBar = "SAP hierarchy alignment" Application.ScreenUpdating = False On Error Resume Next Dim rngcell As Range Sheets("SAP").Activate Range("A2:A25000").Activate For Each rngcell In Selection If rngcell < blank And rngcell.Offset(0, 1) = blank Then Do Until rngcell.Offset(0, 1) < blank rngcell.Offset(0, 1).Delete Shift:=xlToLeft Loop End If Next rngcell Application.StatusBar = "All done" Application.ScreenUpdating = True End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow VBA code....Hide/Unhide Loop | Excel Worksheet Functions | |||
VBA loop slow if another workbook open | Excel Discussion (Misc queries) | |||
deleteing objects | Excel Discussion (Misc queries) | |||
Deleteing | Excel Discussion (Misc queries) | |||
slow program in a loop | Excel Programming |