Help to speed up this macro

I have the following macro that runsa a UDF to delete blank rows (every other
row). The formula is inserted in cell E11 and copied down for for each line
where Col B is not empty. It works just fine, however, there are only 100+
lines and it runs very slowly. Is there something Ican do to speed it up?

Sub BudgetJobNo()
'
Sheets("Budget").Select

Call RowsJoinData(2) '<where column 2 (B) is blank for every row to delete

Application.DisplayAlerts = False
Application.ScreenUpdating = False

JobNo = "=IF(ISERROR(LEFT(Budget!B11,FIND(""
"",Budget!B11)-1))=FALSE,LEFT(Budget!B11,FIND("" "",Budget!B11)-1),"" "")"
Range("E11").Value = JobNo

'And then copy them down to the last line.
Range("e11:e11").Select
Selection.Copy
CR = 11
Do While Cells(CR, 2) <> ""
Range(Cells(CR, 5), Cells(CR, 5)).Select
ActiveSheet.Paste
CR = CR + 1
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub

--
Jim

atpgroups
Fri May 09 04:58:12 CDT 2008

On 9 May, 09:15, Jim G <J...@discussions.microsoft.com> wrote:
> =A0The formula is inserted in cell E11 and copied down for for each line
> where Col B is not empty. =A0It works just fine, however, there are only 1=
00+
> lines and it runs very slowly. =A0Is there something Ican do to speed it u=
p?

A number of things spring to mind..
Do you need the equation in the cells, or just the result?

As a first step to speeding things up, do away with all the copy/paste
stuff.

ie replace this bit ...

> =A0 =A0 JobNo =3D "=3DIF(ISERROR(LEFT(Budget!B11,FIND(""
> "",Budget!B11)-1))=3DFALSE,LEFT(Budget!B11,FIND("" "",Budget!B11)-1),"" ""=
)"
> =A0 =A0 Range("E11").Value =3D JobNo
> =A0 =A0 Range("e11:e11").Select
> =A0 =A0 Selection.Copy
> =A0 =A0 CR =3D 11
> =A0 =A0 Do While Cells(CR, 2) <> ""
> =A0 =A0 =A0 =A0 Range(Cells(CR, 5), Cells(CR, 5)).Select
> =A0 =A0 =A0 =A0 ActiveSheet.Paste
> =A0 =A0 =A0 =A0 CR =3D CR + 1
> =A0 =A0 Loop

With...

JobNo =3D "=3DIF(ISERROR(LEFT(Budget!B11,FIND("""",Budget!
B11)-1))=3DFALSE,LEFT(Budget!B11,FIND("" "",Budget!B11)-1),"" "")"
CR =3D 11
Do While Cells(CR, 2) <> ""
Cells(CR, 5).Value =3D JobNo
CR =3D CR + 1
Loop

Not that I have bothered to test this, so it might be wrong in detail,
though right in principle.

JimG
Fri May 09 06:14:00 CDT 2008

Thanks for that,
I probably don't need the formulas, but I like to chck that users haven't
typed over a result.

I haven't tested you solution but I can see the logic.

In the meantime, I'd manged to come up with this whcih seemed to do the trick:

Sub BudgetJobNo()
'
' BudgetJobNo Macro
'
Dim myRange As String

Sheets("Budget").Select

Call RowsJoinData(2) '<where column 2 (B) is blank for every row to delete

Application.DisplayAlerts = False
Application.ScreenUpdating = False

lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
myRange = "(" & "E11:E" & lastrow & ")"
JobNo = "=IF(ISERROR(LEFT(Budget!B11,FIND(""
"",Budget!B11)-1))=FALSE,LEFT(Budget!B11,FIND("" "",Budget!B11)-1),"" "")"
Range("E11").Value = JobNo

'And then copy them down to the last line.
Range(myRange).Select
Selection.FillDown
Range("E11").Select

Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub


--
Jim


"atpgroups" wrote:

> On 9 May, 09:15, Jim G <J...@discussions.microsoft.com> wrote:
> > The formula is inserted in cell E11 and copied down for for each line
> > where Col B is not empty. It works just fine, however, there are only 100+
> > lines and it runs very slowly. Is there something Ican do to speed it up?
>
> A number of things spring to mind..
> Do you need the equation in the cells, or just the result?
>
> As a first step to speeding things up, do away with all the copy/paste
> stuff.
>
> ie replace this bit ...
>
> > JobNo = "=IF(ISERROR(LEFT(Budget!B11,FIND(""
> > "",Budget!B11)-1))=FALSE,LEFT(Budget!B11,FIND("" "",Budget!B11)-1),"" "")"
> > Range("E11").Value = JobNo
> > Range("e11:e11").Select
> > Selection.Copy
> > CR = 11
> > Do While Cells(CR, 2) <> ""
> > Range(Cells(CR, 5), Cells(CR, 5)).Select
> > ActiveSheet.Paste
> > CR = CR + 1
> > Loop
>
> With...
>
> JobNo = "=IF(ISERROR(LEFT(Budget!B11,FIND("""",Budget!
> B11)-1))=FALSE,LEFT(Budget!B11,FIND("" "",Budget!B11)-1),"" "")"
> CR = 11
> Do While Cells(CR, 2) <> ""
> Cells(CR, 5).Value = JobNo
> CR = CR + 1
> Loop
>
> Not that I have bothered to test this, so it might be wrong in detail,
> though right in principle.
>

atpgroups
Fri May 09 06:55:30 CDT 2008

On 9 May, 12:14, Jim G <J...@discussions.microsoft.com> wrote:

> =A0 =A0 'And then copy them down to the last line.
> =A0 =A0 Range(myRange).Select
> =A0 =A0 Selection.FillDown
> =A0 =A0 Range("E11").Select

Try to avoid selecting stuff to work on it. If the user clicks a cell
or changes sheets it all goes wrong.

This will do pretty much the same thing as you have done there
regardless of which sheet is at the front, or what else is happening.

Range(myRange, Myrange.end(xlDown)).Value =3D JobNo

Gary
Fri May 09 09:59:15 CDT 2008

try turning off calculation at the beginning and turning it back on when you're
done
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With


' your code

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With

--


Gary


"Jim G" <JimG@discussions.microsoft.com> wrote in message
news:78AB6A88-96B8-4ED2-AA57-09E7AB79EFE8@microsoft.com...
>I have the following macro that runsa a UDF to delete blank rows (every other
> row). The formula is inserted in cell E11 and copied down for for each line
> where Col B is not empty. It works just fine, however, there are only 100+
> lines and it runs very slowly. Is there something Ican do to speed it up?
>
> Sub BudgetJobNo()
> '
> Sheets("Budget").Select
>
> Call RowsJoinData(2) '<where column 2 (B) is blank for every row to delete
>
> Application.DisplayAlerts = False
> Application.ScreenUpdating = False
>
> JobNo = "=IF(ISERROR(LEFT(Budget!B11,FIND(""
> "",Budget!B11)-1))=FALSE,LEFT(Budget!B11,FIND("" "",Budget!B11)-1),"" "")"
> Range("E11").Value = JobNo
>
> 'And then copy them down to the last line.
> Range("e11:e11").Select
> Selection.Copy
> CR = 11
> Do While Cells(CR, 2) <> ""
> Range(Cells(CR, 5), Cells(CR, 5)).Select
> ActiveSheet.Paste
> CR = CR + 1
> Loop
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
>
>
> End Sub
>
> --
> Jim