General discussion

Locked

Macros in Excel

By LF King ·
I have a listing of 80+/- items that I need a macro that will sort and add a blank line between each change. Ex.
Cost Ctr Part#
370 AVE62883
341 ACC74200
371 MMM6809
521 SMD64855
Sort part is fine, but after it sorts I need the macro to add a blank line between each different cost center.

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Macros in Excel

by DKlippert In reply to Macros in Excel

This from David McRitchie

www.mvps.org/dmcritchie/excel/insrtrow.htm

Select the range and then run this code.

Sub InsertRow_A_Chg()
Dim irow As Long, vcurrent As String, i As Long
'****find last used cell in Column A
irow = Cells(Rows.Count, "A").End(xlUp).Row
'****get value of that cell in Column A (column 1)
vcurrent = Cells(irow, 1).Value
'****rows are inserted by looping from bottom
For i = irow To 2 Step -1
If Cells(i, 1).Value <> vcurrent Then
vcurrent = Cells(i, 1).Value
Rows(i + 1).Insert
End If
Next i
End Sub

Collapse -

Macros in Excel

by LF King In reply to Macros in Excel

Poster rated this answer

Collapse -

Macros in Excel

by LF King In reply to Macros in Excel

So far so good, just need to change one thing and I haven't got it right yet. Here's how it looks
ActiveSheet.Unprotect
Application.Goto Reference:="SortInsert"
Selection.Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Dim irow As Long, vcurrent As String, i As Long
'****find last used cell in Column A
irow = Cells(Rows.Count, "A").End(xlUp).Row '****get value of that cell in Column A (column 1)
vcurrent = Cells(irow, 1).Value
'****rows are inserted by looping from bottom
For i = irow To 2 Step -1
If Cells(i, 1).Value <> vcurrent Then
vcurrent = Cells(i, 1).Value
Rows(i + 1).Insert
End If
Next i
Range("G70").Select
ActiveCell.FormulaR1C1 = "Total"
Range("J70").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-64]C:R[-1]C)"
Range("G71").Select
Application.Goto Reference:="R1C1"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

On the row '****find last cell used in Column A
I need it to start on row 6, the first 5 rows are headers.

Collapse -

Macros in Excel

by LF King In reply to Macros in Excel

This question was closed by the author

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums