Sleeper: Inserting (not entire) rows after groups (excel 97)

[Shadowmis]

Hey, I am working with Excel 97. mdmackillop kindly showed me how to do a sort and then add rows after groups, but I am wondering if I can add row only within the selection and not an entire row. You see, I want to set up two reports beside each other and have each do its own sort and add row but since they are side by side, inserting an entire blank row in one messes up the other.
What is the trick?
mdmackillop's macro


Sub SplitData()

Range("A4:H4").Select
Range(Selection, Selection.End(xlDown)).Select
'Replace this section
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= False, Orientation:=xlTopToBottom
ActiveCell.Select
For i = 5 To ActiveSheet.UsedRange.Rows.Count
tmp1 = Int(Cells(i, 1) / 1000)
tmp2 = Int(Cells(i - 1, 1) / 1000)
If tmp1 - tmp2 >= 1 And tmp2 <> 0 Then
Cells(i, 1).EntireRow.Insert
End If
If Cells(i + 1, "A") <> 2 Then
Cells(i + 1, 1).EntireRow.Insert
Exit Sub
End If
Next
End Sub

[Ken Puls]

Hi Shadowmis,

Just to let you know, I've edited your post to display your code using VBA tags. This helps us read it a little more easily.

If I've read your request correctly, I think that you could change this:


Cells(i + 1, 1).EntireRow.Insert
To this:

Cells(i + 1, 1).Resize(1,8).Insert shift:=xlDown

That will insert cells in columns A:H (H = 8) and shift the cells down.

HTH,

[Shadowmis]

I will remember to put it in VBA tags next time thanks...
As for the code it does only insert rows a to h (yeah!) but the rows are being inserted one after where they are suppose to go. I can and did fix it by changing it to

Cells(i, 1).Resize(1, 8).Insert shift:=xlDown
However, in the spirit of learning I am not sure why changing it to resize would change that aspect of the original code. ?!?

Now a new problem I get a type mismatch error msg in this first line ( and will probably get the same error on the second line as well)

tmp1 = Int(Cells(i, 1) / 1000)
tmp2 = Int(Cells(i - 1, 1) / 1000)
But not untill after it has gone through the information and inserted the blank rows.
What am I missing?
(added later) I now get the same error '13' type mismatch for the same lines when I tried using the code on the other section, but this time it does not go through the data first.

[mdmackillop]

You can use the following types of references to insert single rows or blocks of cells.


Cells(3, 1).Range("A1:E1").Insert shift:=xlDown


Cells(3, 1).Range("A1:E5").Insert shift:=xlDown


ActiveCell.Offset(5, -3).Range("A1:G3").Insert shift:=xlDown

[Shadowmis]

Here is the file I am working on. The explanation for the type mismatch error says it is trying to match a string with a boolean. However, the next line is an if statement which makes a true false of tmp1 and tmp2 which I made As Long. Whereis it trying to link the string to the boolean except in the if function?
Also if I try to sort the second section first it starts the sort and insert and then error but if I sort the first section and then the second section I just get the error right away.
:dunno

[Ken Puls]

Aha!

In your example, it fails when cells(i,1) is blank. This is because cells("",1) is not a valid reference.

Try this to start:


Private Sub CommandButton1_Click()
Dim i As Long, tmp1 As Long, tmp2 As Long
Range("A6:H6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, _
Header:=xlGuess, MatchCase:=False, Orientation:=xlTopToBottom
ActiveCell.Select
For i = 7 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, 1).Value = vbNullString Then Exit Sub
tmp1 = Int(Cells(i, 1) / 1000)
tmp2 = Int(Cells(i - 1, 1) / 1000)
If tmp1 - tmp2 >= 1 And tmp2 <> 0 Then
Cells(i, 1).Resize(1, 8).insert shift:=xlDown
End If
Next
End Sub

May not solve all the issues, but should get you started.

HTH,

[mdmackillop]

The error results from dividing an empty string "" by 1000. Try the following


Private Sub CommandButton1_Click()
Dim i As Long, tmp1 As Long, tmp2 As Long
Range("A6:H" & [A6].End(xlDown).Row()).Sort Key1:=Range("A6")
i = 7
Do
tmp1 = Int(Cells(i, 1) / 1000)
tmp2 = Int(Cells(i - 1, 1) / 1000)
If tmp1 - tmp2 >= 1 And tmp2 <> 0 Then
Cells(i, 1).Resize(1, 8).insert shift:=xlDown
End If
i = i + 1
Loop Until Cells(i, 1) = ""
End Sub

[Shadowmis]

I have tried both suggestions, and both get rid of the type mismatch (Thank You). The first section works like a peach (yeah!!:), but the second one (again) is giving me problems. Neither suggestion inserts the lines at the right place.. I am wondering if, as well as changing where it looks to make the selection before sorting, I have to change 'i' as well? and if so how?

[mdmackillop]

Sorry, missed the bit about table 2:(


Private Sub CommandButton1_Click()
Dim i As Long, tmp1 As Long, tmp2 As Long
'Table 1
Range("A6:H" & [A6].End(xlDown).Row()).Sort Key1:=Range("A6")
i = 7
Do
tmp1 = Int(Cells(i, "A") / 1000)
tmp2 = Int(Cells(i - 1, "A") / 1000)
If tmp1 - tmp2 >= 1 And tmp2 <> 0 Then
Cells(i, "A").Resize(1, 8).insert shift:=xlDown
End If
i = i + 1
Loop Until Cells(i, 1) = ""
'Table 2
Range("J6:N" & [J6].End(xlDown).Row()).Sort Key1:=Range("J6")
i = 7
Do
tmp1 = Int(Cells(i, "J") / 1000)
tmp2 = Int(Cells(i - 1, "J") / 1000)
If tmp1 - tmp2 >= 1 And tmp2 <> 0 Then
Cells(i, "J").Resize(1, 5).insert shift:=xlDown
End If
i = i + 1
Loop Until Cells(i, "J") = ""
End Sub

[Shadowmis]

Aha! I see, now I understand what the the 1 meant, and to make it work for both by replacing replacing with "A" and "J".
The learning process moves a little bit further:)
Cool,
Thank You

[mdmackillop]

There are a few ways of referring to cells, Cells(i,j) allows you to code for both row and columns changing, or you can fix one value with a number or letter as appropriate. If you had a lot of table to change, you could set up an array to hold your references, A6, J6, P6 etc. and pass these as variables to a looping code.
Glad to be of help.