Prev: Alien abduction of VB: "Code execution has been interrupted..."
Next: Enumerate all global variables?
From: KWhamill on 17 Jul 2008 15:10
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
From: ND Pard on 17 Jul 2008 15:18
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.

"KWhamill" wrote:

> 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
From: macgru on 17 Jul 2008 15:21
use subtotals
it will add row below data you choose as changing value
mcg

U�ytkownik "KWhamill" <KWhamill(a)discussions.microsoft.com> napisa� w
wiadomo�ci news:DEE8BA58-9B68-4F5B-830B-2B688D8E969E(a)microsoft.com...
> 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


From: Gord Dibben on 17 Jul 2008 22:40
>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

On Thu, 17 Jul 2008 12:10:06 -0700, KWhamill
<KWhamill(a)discussions.microsoft.com> wrote:

>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

From: KWhamill on 18 Jul 2008 09:07
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

"Gord Dibben" wrote:

> >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
>
> On Thu, 17 Jul 2008 12:10:06 -0700, KWhamill
> <KWhamill(a)discussions.microsoft.com> wrote:
>
> >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
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Alien abduction of VB: "Code execution has been interrupted..."
Next: Enumerate all global variables?