Compare cells and insert row

[Guest]

After sorting, how is the best way to compare two adjacent cells in a column,
and if they are different, insert a blank row and a header row between them.

 

[Dave Peterson]

If possible, you may want to use excel's built in Data|subtotal.

(Put the header in either rows to repeat at top or in File|page
setup|header/footer.)

Data|Subtotal does have an option to put a "page break between groups".

=======
And my own personal preference is to never insert blank rows. I will double the
rowheight to make it look like it's double spaced, though. (Blank rows make
everything more difficult--subtotals, charts, pivottables.)

And I'd only use one set of headers (for printing, use rows to repeat at top.
For viewing, use window|freeze panes.)

If you really want more control than data|subtotal gives you, maybe you can
modify this:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Set wks = Worksheets("sheet1")
With wks
.ResetAllPageBreaks 'remove them all to start
FirstRow = 2 'headers in 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
'do nothing
Else
' .Rows(iRow).Insert
'not sure what headers mean
.Rows(iRow).RowHeight = .Rows(iRow).RowHeight * 2
If iRow > 1 Then
.HPageBreaks.Add before:=.Cells(iRow, "A")
End If
End If
Next iRow
End With
End Sub

 

[Guest]

After sorting, how is the best way to compare two adjacent cells in a column,
and if they are different, insert a blank row and a header row between them.

Tom,
Not completely sure about the question, but to insert rows and clolumns you
can use the following code

Rows("XX:XX").Select
Selection.Insert Shift:=xlDown
Columns("XX:XX").Select
Selection.Insert Shift:=xlToRight

as for reading cells
workbooks("XXXXX").worksheets("XXXXXX").cells(XX, XX).value
or if your not changeing workbooks and/or worksheets then just simple
cells(XX, XX).value
works

if this isn't mutch help you can always record a macro of you doing what you
want to do and then view and edit the code to fit your needs

 

[Guest]

Have have rows of data for deviations. I sort by Close date and delete all
rows that have been closed. I then sort by department. What i want to do
next is insert a blank row and copy the first row in the first row so that it
is easy to cut and paste the data for each department and email to those
departments. I'm having trouble with an if statement that compares the
active cell with the next or previous (which ever is easiest), and if they
are different, insert the blank and header. The blank row is not abosilutely
necessary. It just helps to cut and paste into the email.

This is what I was trying:

Dim cRowsB As Integer
Dim j As Integer

cRowsB = Cells(Rows.Count, 2).End(xlUp).Row
Range("G3").Select

For j = 3 To cRowsB
If Cell(G, j).Value = Cell(G, j - 1).Value Then
Rows("j:j").Select
Selection.Insert Shift:=xlDown
Rows("1:1").Select
Selection.Copy
Rows("j:j").Select
Selection.Insert Shift:=xlDown
Selection.Paste
ActiveCell.Offset(1, 0).Select
End If
Next j

Thanks for any help. I'm a really not good at VB.

 

[Dave Peterson]

When you use Cells(X,Y) to refer to a range, the X represents the row and the Y
represents the column.

And rows are numbered (1, 2, ..., 65536) and columns are usually lettered (A, B,
...., IV) <but there's a setting that can show numbers (1, 2, ..., 256).

And this won't work:
Rows("j:j").Select

But if you could use:

rows(j & ":" & j).select
or even
rows(j).select

But it's not necessary to select most things to work with them.

I wasn't sure what column contained the key value, so I guessed column A.

Try this against a copy of the worksheet.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Set wks = Worksheets("sheet1")
With wks
FirstRow = 2 'headers in 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
'do nothing
Else
.Rows(iRow).Resize(2).Insert
.Rows(1).Copy _
Destination:=.Cells(iRow + 1, "A")
End If
Next iRow
End With
End Sub