sort and then insert rows help!

[RompStar]

I am trying to start to automate this painful report that I do by hand
every monday, I kinda was noticing what manual steps I was doing and I
figure I try to automate some of it, maybe all of it with time :- )

So I wanted to do first is.

The excel sheet has 6 columns, A - F

A--------------B------------C
Date-----------Name---------Activity


D E and F have time and productivity data, but I only want to sort by A
first and then by B, this I already done, so Sort by Date, then by
Name. Then Look into column B and insert a 25 row space between the
Names if the are not the same..

Here is my VB code so far: the Sorting works (maybe there is a way to
shorten the code), but it works..

The insert 25 rows, doesn't work, so I am figuring there is something
wrong there, that's the part I need help with..


Sub FormatTablebyName()

' This one will sort the column A/B and then seperate by 25 rows column
B if name is not the same.

Dim iRow As Long
Dim lastRow As Long

lastRow = Range("A65536").End(xlUp).Row

Columns("A:F").Select
Range("F1").Activate

' Sort Column A ( Works )

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

' Sort Column B ( Works )

Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

' Seperate Names in Column B with a 25 space row insert

For iRow = 1 To lastRow
If Cells(iRow, 2) <> Cells(iRow + 1, 2) Then
Rows(iRow + 25).Insert
lastRow = lastRow + 25
Row = iRow + 25
End If
Next iRow
End Sub

If I replace the 25 with a 1, it works perfectly for inserting the 1
row insert, but not with 25, what am I doing wrong, help this niewbie
out, thanks!

[Dave Peterson]

You could sort your data by both columns in one step (using that secondary sort
field).

And if you work from the bottom up, life becomes lots easier:

Option Explicit

Sub FormatTablebyName()

Dim iRow As Long
Dim lastRow As Long

With ActiveSheet
lastRow = .Range("A65536").End(xlUp).Row

.Columns("A:F").Sort _
Key1:=.Range("A1"), Order1:=xlAscending, _
Key2:=.Range("B1"), Order2:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

For iRow = lastRow To 2 Step -1
If .Cells(iRow, 2) <> .Cells(iRow - 1, 2) Then
.Rows(iRow).Resize(25).Insert
End If
Next iRow
End With

End Sub





RompStar wrote:
>
> I am trying to start to automate this painful report that I do by hand
> every monday, I kinda was noticing what manual steps I was doing and I
> figure I try to automate some of it, maybe all of it with time :- )
>
> So I wanted to do first is.
>
> The excel sheet has 6 columns, A - F
>
> A--------------B------------C
> Date-----------Name---------Activity
>
> D E and F have time and productivity data, but I only want to sort by A
> first and then by B, this I already done, so Sort by Date, then by
> Name. Then Look into column B and insert a 25 row space between the
> Names if the are not the same..
>
> Here is my VB code so far: the Sorting works (maybe there is a way to
> shorten the code), but it works..
>
> The insert 25 rows, doesn't work, so I am figuring there is something
> wrong there, that's the part I need help with..
>
> Sub FormatTablebyName()
>
> ' This one will sort the column A/B and then seperate by 25 rows column
> B if name is not the same.
>
> Dim iRow As Long
> Dim lastRow As Long
>
> lastRow = Range("A65536").End(xlUp).Row
>
> Columns("A:F").Select
> Range("F1").Activate
>
> ' Sort Column A ( Works )
>
> Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
>
> ' Sort Column B ( Works )
>
> Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
>
> ' Seperate Names in Column B with a 25 space row insert
>
> For iRow = 1 To lastRow
> If Cells(iRow, 2) <> Cells(iRow + 1, 2) Then
> Rows(iRow + 25).Insert
> lastRow = lastRow + 25
> Row = iRow + 25
> End If
> Next iRow
> End Sub
>
> If I replace the 25 with a 1, it works perfectly for inserting the 1
> row insert, but not with 25, what am I doing wrong, help this niewbie
> out, thanks!

--

Dave Peterson

[RompStar]

yep, yep, I was just waiting for you to help me, lol

I have learnd so much from you already :- ) taking advantage of a
education
reimbursement program here at work, so I started to go to college for
database administration, hopefully I will learn a lot more in the next
3-4 years :- )

Thanks a lot!

[RompStar]

I'll test it on monday and tell you if there is anything weird going
on, I'll step into it and follow it line by line to
learn new things, thanks.

[Dave Peterson]

It's a nice benefit. Take advantage of it while it's still there.

RompStar wrote:
>
> yep, yep, I was just waiting for you to help me, lol
>
> I have learnd so much from you already :- ) taking advantage of a
> education
> reimbursement program here at work, so I started to go to college for
> database administration, hopefully I will learn a lot more in the next
> 3-4 years :- )
>
> Thanks a lot!

--

Dave Peterson

[RompStar]

so I commented out the insert row part of the vb code and only wanted
to see if the sorting works,
it sorts, but not correctly, it sorts the same name maybe 5 rows and
then it goes to the next,
and then it eventually starts to sort the same name again, it doens't
sort them all
in one shot and then go to the next, the sort is kinda broken apart
sort, not even all the way through ?

any ideas why ?

[Dave Peterson]

Record a macro when you sort by both keys.

Then modify this portion:

.Columns("A:F").Sort _
Key1:=.Range("A1"), Order1:=xlAscending, _
Key2:=.Range("B1"), Order2:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Maybe it's just swapping key1 and key2?????

RompStar wrote:
>
> so I commented out the insert row part of the vb code and only wanted
> to see if the sorting works,
> it sorts, but not correctly, it sorts the same name maybe 5 rows and
> then it goes to the next,
> and then it eventually starts to sort the same name again, it doens't
> sort them all
> in one shot and then go to the next, the sort is kinda broken apart
> sort, not even all the way through ?
>
> any ideas why ?

--

Dave Peterson