Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a faster way
NewLRow below = 7170
As a result this Macro runs for over 25 minutes; Is there a faster way of getting this done? Sub Macro3() Application.ScreenUpdating = False Dim NewLRow As Long Dim r As Long NewLRow = Cells(Rows.Count, 9).End(xlUp).Row For r = NewLRow To 2 Step -1 If Cells(r, 9) = "" Then Rows(r).Delete End If Next Range("A1").Select Selection.EntireRow.Delete Selection.EntireColumn.Delete Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a faster way
If the cells in the column are blank
Application.Calculation = xlManual Columns(9).SpecialCells(xlBlanks).entireRow.Delete Application.Calculation = xlAutomatic -- Regards, Tom Ogilvy "Jim May" wrote in message news:TF53d.325828$Oi.259881@fed1read04... NewLRow below = 7170 As a result this Macro runs for over 25 minutes; Is there a faster way of getting this done? Sub Macro3() Application.ScreenUpdating = False Dim NewLRow As Long Dim r As Long NewLRow = Cells(Rows.Count, 9).End(xlUp).Row For r = NewLRow To 2 Step -1 If Cells(r, 9) = "" Then Rows(r).Delete End If Next Range("A1").Select Selection.EntireRow.Delete Selection.EntireColumn.Delete Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a faster way
Thanks Tom;
Would restricting the range down from Columns(9) [all rows 1-65000+] to say Range(I1:I7071) shorten the time even more? like: Range(I1:I7071).SpecialCells(xlBlanks).entireRow.D elete ? Thanks, JMay "Tom Ogilvy" wrote in message ... If the cells in the column are blank Application.Calculation = xlManual Columns(9).SpecialCells(xlBlanks).entireRow.Delete Application.Calculation = xlAutomatic -- Regards, Tom Ogilvy "Jim May" wrote in message news:TF53d.325828$Oi.259881@fed1read04... NewLRow below = 7170 As a result this Macro runs for over 25 minutes; Is there a faster way of getting this done? Sub Macro3() Application.ScreenUpdating = False Dim NewLRow As Long Dim r As Long NewLRow = Cells(Rows.Count, 9).End(xlUp).Row For r = NewLRow To 2 Step -1 If Cells(r, 9) = "" Then Rows(r).Delete End If Next Range("A1").Select Selection.EntireRow.Delete Selection.EntireColumn.Delete Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a faster way
Probably not. In this context, specialcells should not select beyond the
UsedRange. If your UsedRange extends well beyond the last filled cell, then you might use what you suggest or use it any way: in any event it certainly shouldn't make it slower I wouldn't think. You can test it. Sub AAAC() Dim rng As Range Set rng = Columns(9).SpecialCells(xlBlanks) Set rng = rng.Areas(rng.Areas.Count) Application.Goto rng(rng.Count) End Sub to see what the last cell addressed is. -- Regards, Tom Ogilvy "Jim May" wrote in message news:4Z63d.325831$Oi.291840@fed1read04... Thanks Tom; Would restricting the range down from Columns(9) [all rows 1-65000+] to say Range(I1:I7071) shorten the time even more? like: Range(I1:I7071).SpecialCells(xlBlanks).entireRow.D elete ? Thanks, JMay "Tom Ogilvy" wrote in message ... If the cells in the column are blank Application.Calculation = xlManual Columns(9).SpecialCells(xlBlanks).entireRow.Delete Application.Calculation = xlAutomatic -- Regards, Tom Ogilvy "Jim May" wrote in message news:TF53d.325828$Oi.259881@fed1read04... NewLRow below = 7170 As a result this Macro runs for over 25 minutes; Is there a faster way of getting this done? Sub Macro3() Application.ScreenUpdating = False Dim NewLRow As Long Dim r As Long NewLRow = Cells(Rows.Count, 9).End(xlUp).Row For r = NewLRow To 2 Step -1 If Cells(r, 9) = "" Then Rows(r).Delete End If Next Range("A1").Select Selection.EntireRow.Delete Selection.EntireColumn.Delete Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I print faster? | Excel Discussion (Misc queries) | |||
can this be done faster? | Excel Discussion (Misc queries) | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) | |||
Which one is faster? | Excel Programming | |||
Shorter and faster? | Excel Programming |