Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inefficient Space Removal
Hi There,
I have recently cobbled together some code to strip out empty cells from a row of information, unfortunately it runs extremely slowly, could anyone suggest a way of making this following code more efficient so it doesen't take 2mins+ to run? Sub RemEmptyFinal() Application.ScreenUpdating = False Dim rng As Range, ix As Long 'Sheet1 Sheets("AOwens").Select Sheet1.Range("W2:W1000").Select Selection.Copy Sheet1.Range("U2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ True, Transpose:=False Set rng = Intersect(Selection, ActiveSheet.UsedRange) If rng Is Nothing Then GoTo done End If -For ix = rng.Count To 1 Step -1 If Len(Trim(Replace(rng.Item(ix).Formula, Chr(160), ""))) _ = 0 Then rng.Item(ix).Delete (xlUp)- Sheet1.Range("A1").Select Next ~*~*~* Thanks all, I am extremely grateful for any suggestions. Sau --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inefficient Space Removal
Take out the
Sheet1.Range("A1").Select -- Regards, Tom Ogilvy "Sauron" wrote in message ... Hi There, I have recently cobbled together some code to strip out empty cells from a row of information, unfortunately it runs extremely slowly, could anyone suggest a way of making this following code more efficient so it doesen't take 2mins+ to run? Sub RemEmptyFinal() Application.ScreenUpdating = False Dim rng As Range, ix As Long 'Sheet1 Sheets("AOwens").Select Sheet1.Range("W2:W1000").Select Selection.Copy Sheet1.Range("U2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ True, Transpose:=False Set rng = Intersect(Selection, ActiveSheet.UsedRange) If rng Is Nothing Then GoTo done End If -For ix = rng.Count To 1 Step -1 If Len(Trim(Replace(rng.Item(ix).Formula, Chr(160), ""))) _ = 0 Then rng.Item(ix).Delete (xlUp)- Sheet1.Range("A1").Select Next ~*~*~* Thanks all, I am extremely grateful for any suggestions. Sau --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inefficient Space Removal
also, Turn calculation to manual.
-- Regards, Tom Ogilvy "Sauron" wrote in message ... Hi There, I have recently cobbled together some code to strip out empty cells from a row of information, unfortunately it runs extremely slowly, could anyone suggest a way of making this following code more efficient so it doesen't take 2mins+ to run? Sub RemEmptyFinal() Application.ScreenUpdating = False Dim rng As Range, ix As Long 'Sheet1 Sheets("AOwens").Select Sheet1.Range("W2:W1000").Select Selection.Copy Sheet1.Range("U2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ True, Transpose:=False Set rng = Intersect(Selection, ActiveSheet.UsedRange) If rng Is Nothing Then GoTo done End If -For ix = rng.Count To 1 Step -1 If Len(Trim(Replace(rng.Item(ix).Formula, Chr(160), ""))) _ = 0 Then rng.Item(ix).Delete (xlUp)- Sheet1.Range("A1").Select Next ~*~*~* Thanks all, I am extremely grateful for any suggestions. Sau --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inefficient Space Removal
Try this.
Sub DeleteBlanks() Range("A1:A20").SpecialCells(xlCellTypeBlanks).Del ete Shift:=xlUp End Sub I'll admit it's not my work, I was using a loop method but someone else suggested this as a better alternative. HTH Chris |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inefficient Space Removal
Excel doesn't view a cell with Chr(160) as blank, so I doubt this will work
as a general solution. -- Regards, Tom Ogilvy "Chris Leonard" wrote in message ... Try this. Sub DeleteBlanks() Range("A1:A20").SpecialCells(xlCellTypeBlanks).Del ete Shift:=xlUp End Sub I'll admit it's not my work, I was using a loop method but someone else suggested this as a better alternative. HTH Chris |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inefficient Space Removal
Cheers for the quick responses!
Thanks Chris I tried using your code but it didn't work :( never mind I'll just have to keep on the lookout for a better way. I tried what you said Tom and I think I am getting a slight decrease in the time taken but its still taking 1min+ unfortunately... Never mind, it still beats doing it manually! :D Thanks again, Sau --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Space removal - a first name,space last name | Excel Discussion (Misc queries) | |||
Inefficient Calculation of User Defined Functions in Excel 2000 | Excel Programming | |||
Loop worked but is it inefficient? | Excel Programming | |||
Loop worked but is it inefficient? | Excel Programming | |||
Inefficient VBA Code - Worksheets Property | Excel Programming |