Insert a blank row

[KWhamill]

This sounds silly, I have a spreadsheet sorted by column I which is text, all
i need to do is insert a blank row inbetween the changes. Or If some one
could help me figure out another way to undo the remove blank row instruction
at the beginning of the code.
respectfully,
Karl

 

[ND Pard]

After column I has been sorted, add a numeric column immediately to the right
of your data. You can place 1 in each cell of this new column or even 0 or
any other numeric value.

Then do a subtotal on this new column based on each time column I changes.

You can then delete the new column if you want ... but you will have a blank
line between each change in column I.

Good Luck.

 

[Gord Dibben]

Or If some one

could help me figure out another way to undo the remove blank row instruction
at the beginning of the code.

Which code would that be.................you did not post any code.

Try this macro.

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 3 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
Cells(X, 1).entirerow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

 

[KWhamill]

Good question.
What follows is the code to delete the empty rows :
Dim LastRow As Long, r As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
LastRow = LastRow + ActiveSheet.UsedRange.Row - 1
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
The code I'm using to put them back is this:
Dim i As Long
Const col As String = "I"

Application.ScreenUpdating = False

For i = Selection.Rows.Count To 2 Step -1
With Cells(i, col)
If .Value <> .Offset(-1).Value Then
.EntireRow.Insert
End If
End With
Next i

Application.ScreenUpdating = True

It works but, It isn't porfect and for reasons i don't understand if I use
the With . . .End With statement in the first part it won't work in the
second.
I'll try some of the modifications you're suggestiong though they look
interesting.
Thanks,
Karl

 

[KWhamill]

Maybe you can help me with this problem. How can i transform the code that
follows to delete a row if the value in Column C is less than 0.01?

Dim LastRow As Long, r As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
LastRow = LastRow + ActiveSheet.UsedRange.Row - 1
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r