Excel macro to aid visual clarity?

[Victor Delta]

I have a spreadsheet of customers' data. One of the columns contains the
customers' names and , when sorted, shows the same names row after row.

In order to aid clarity when reviewing or printing the sheet, I am wondering
if it is possible to write a macro which will go down the column selected by
the cursor (customers' names) and insert two blank rows every time the name
changes - to effectively create blocks of data for each customer. (I can
resort the data to get it back together).

I would like the macro to work on the column selected by the cursor so that
I can use the same macro to do the same process when the sheet is sorted by
a different column's data (e.g. products).

Many thanks,

V

 

[Dave Peterson]

You could insert a couple of lines after each break in customer names, but I
wouldn't do it.

Instead, I'd add a little bit to the rowheight. It would make the reading
easier, but not change the data (by inserting extra rows).

If you want to try:

Option Explicit
Sub testme()

Dim myAdjustment As Double
Dim myCol As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long

myAdjustment = 12

With ActiveSheet
myCol = ActiveCell.Column

FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, myCol).End(xlUp).Row

.UsedRange.Rows.AutoFit 'reset the rowheights to "normal"

For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, myCol).Value = .Cells(iRow - 1, myCol).Value Then
'same, do nothing
Else
.Rows(iRow).RowHeight = .Rows(iRow).RowHeight + myAdjustment
End If
Next iRow

End With

End Sub

I used 12 as the adjustment. You may want something different.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)


If you're really industrious, you could record a macro (and then modify it) to
sort the data by the activecell's column, too.

 

[Victor Delta]

Dave

Very many thanks for that. I can see exactly where you are coming from re
increasing the row height but in this particular instance, I really do need
to insert a couple of blank rows.

Any ideas on how to do that please?

V

 

[Gord]

Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 2 Step -1
If Cells(X, 1).Value <> Cells(X - 1, 1).Value Then
If Cells(X, 1).Value <> "" Then
If Cells(X - 1, 1).Value <> "" Then
With Cells(X, 1).Resize(2, 1)
.EntireRow.Insert Shift:=xlDown
End With
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben Microsoft Excel MVP

 

[Victor Delta]

Gord

Many thanks for that. However, I think it's operating on Col A so I'm
getting double rows between every line of the spreadsheet. How can I change
it so it operates on the column of the active cell i.e. where the cursor is?

V

 

[Gord]

If the column with the names is always the same just change

LastRow = Cells(Rows.Count, 1).End(xlUp).Row to (Rows.Count, y) where

y is the column number.............B is 2, C is 3 etc.

If the names column is variable then the following will use the
activecell.column.

Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
Dim y As Long
y = ActiveCell.Column
LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 2 Step -1
If Cells(X, y).Value <> Cells(X - 1, y).Value Then
If Cells(X, y).Value <> "" Then
If Cells(X - 1, y).Value <> "" Then
With Cells(X, y).Resize(2, 1)
.EntireRow.Insert Shift:=xlDown
End With
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord

 

[Victor Delta]

Gord

Many thanks indeed. It was the variable option I was looking for - as per my
original post - and your version of Sandy Mann's macro now does the job
superbly.

Thanks again,

V

--

 

[Gord]

Happy to assist.


Gord