simplify code

Hi,

I'm pretty new to writing code, so I wanted to ask for some advice. I've got
a code which checks to see if cells in two columns are equal, and if they are
not, it inserts blank cells in three columns. It works, but it is pretty
slow, as I have written three separate IF statements for inserting blank
cells. Is there any way that I can condense this into one IF statement? the
code I am using so far is:

LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row
Range("E1").Select
For j=1 To LastRow
If Range("b" & j) <> Range("a" & j) Then Range("b" & j).Select
Selection.Insert Shift:=xlDown
If Range("b" & j) <> Range("a" & j) Then Range("c" & j).Select
Selection.Insert Shift:=xlDown
If Range("b" & j) <> Range("a" & j) Then Range("d" & j).Select
Selection.Insert Shift:=xlDown
Range("e" & j).Select
Next j

Any help would be greatly appreciated.

Looks like you can condense it like this:

LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row
Range("E1").Select
For j=1 To LastRow
If Range("b" & j) <> Range("a" & j) Then
Range("b" & j).Select
Selection.Insert Shift:=xlDown
Range("c" & j).Select
Selection.Insert Shift:=xlDown
Range("d" & j).Select
Selection.Insert Shift:=xlDown
Range("e" & j).Select
Next j

I suspect the length of time required to run your code is not due to
the quantity of IF statements, however. More likely the duration is
due to the spreadsheet recalculating itself every time one of the
insert / shift down operations is performed. You might try the
following code at the beginning of your macro to turn calculation from
auto to manual:

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

.... and then this code at the end to turn calculation back to auto:
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With

Dave,

I don't think that the automatic/manual setting can speed up the program at
all, because this data has not yet been used in any calculations, there are
only a few columns of data at this point. However, I am performing this on
835 rows, so maybe it won't get any faster with less IF statements. However,
for whatever reason, when I tried the code you suggested, I got a an error
about the Next, saying

Error: Next without For.

Does anyone know what this means, and how to bypass the problem. The code
works fine using three separate IF statements, it just seems like there
should be a way to condense them into one.

Hi Matt,
Suggest you try turning off both Calculation and ScreenUpdating
and yes you do need both. You are inserting cells or rows so you
have to recalculate the sheet, and you are changing your selection,
so both are needed. It is best to write your code if possible to
avoid changing a selection, but regardless that simple change
can run circles around bad coding (that works). Combine good
coding with that and you will run much faster.
http://www.mvps.org/dmcritchie/excel...htm#slowmacros

A means of timing your macro is provided on that same page.

When inserting or deleting rows or in your case cells you want to start from
the bottom and work your way up with Step -1 see some examples in
http://www.mvps.org/dmcritchie/excel...nsertBlankRows

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"matt" <[email protected]> wrote in message news:[email protected]...
> Dave,
>
> I don't think that the automatic/manual setting can speed up the program at
> all, because this data has not yet been used in any calculations, there are
> only a few columns of data at this point. However, I am performing this on
> 835 rows, so maybe it won't get any faster with less IF statements. However,
> for whatever reason, when I tried the code you suggested, I got a an error
> about the Next, saying
>
> Error: Next without For.
>
> Does anyone know what this means, and how to bypass the problem. The code
> works fine using three separate IF statements, it just seems like there
> should be a way to condense them into one.
>
>