Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a blank row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a blank row
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a blank row
use subtotals
it will add row below data you choose as changing value mcg Użytkownik "KWhamill" napisał w wiadomo¶ci ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a blank row
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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a blank row
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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a blank row
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 "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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I insert a true blank inst. of a non-blank zero string | Excel Worksheet Functions | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
Macro to insert copy and insert formulas only to next blank row | Excel Programming | |||
Macro code to test for blank row and insert blank row if false | Excel Programming | |||
Insert blank row | Excel Programming |