Comparing values in a cell and looping

[Michael_Randall]

I'm somewhat inexperienced at programming - just learning, but here is what I
would like to do.

I have a spreadsheet and in the first column I have different department
numbers. I have sorted by department number so all number that are the same
are together.

I would like to start out at cell (A6) and get the value of the cell and the
row number. I would then like to move to the next cell down and determine if
the value is the same as the previous cell or blank.

I would continue down the cells until the value in the tested cell is
different from the previous cell or cell A6. If the value is different, I
would like to add a row above that cell/row.

I would then want to use then want to record the different value and use it
as the test value and repeat the process until I get to last row of the
spreadsheet. This is a report so each report will have a different number
rows.

I know how to get the row number of a selected cell and the value of a
selected cell. I just don't know how to do the looping part. I've seen where
they define a range of cells but since each report will contain a different
number of records, not sure how I could do that.

Any help would be greatly appreciated.

Thanks

 

[Rick Rothstein]

You will want to iterate the cells in Column A starting with the last data
row and proceed to the first data row. If you start from the first data row,
then every time you insert a row, you will "mess up" the loop counter's
tracking of the rows. Here is some code to do what you want...

Sub InsertBlankRows()
Dim X As Long, LastRow As Long
Const StartRow = 6
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = LastRow To StartRow Step -1
With Cells(X, "A")
If .Value <> .Offset(-1).Value Then .Insert
End With
Next
End Sub