inserting a row to every second line. 
Author Message
 inserting a row to every second line.

Hi I am trying to insert a new line and gray the (new inserted) line then
move down and format the next line so that the spreadsheet looks like every
second line is greyed and blank.

I want to apply this to the first 1000 lines and am trying to use the count
command with the following code.

Sub ChkFirstWhile()
    counter = 1
    myNum = 1
    Do While myNum < 1000
       Rows ("counter:counter").Select
    Selection.Insert Shift:=xlDown
    Selection.Interior.ColorIndex = 15
    'End With
        myNum = myNum - 1
        counter = counter + 1
    Loop
    'MsgBox "The loop made " & counter & " repetitions."
End Sub

Only problem is that excel does not like my count command.

Any suggestions?



Mon, 17 Nov 2003 07:23:48 GMT  
 inserting a row to every second line.
Hi Chris,
Try changing your macro to start from the bottom.  That works
best for inserting and deleting rows since you are not affecting
the location you currently reference.    STEP -1

You can also use OFFSET to refer to a different line.l

You should be able to figure it out, from this if not poast [sic] back.

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros:  http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page:        http://www.geocities.com/davemcritchie/excel/search.htm


Quote:
> Hi I am trying to insert a new line and gray the (new inserted) line then
> move down and format the next line so that the spreadsheet looks like
every
> second line is greyed and blank.

> I want to apply this to the first 1000 lines and am trying to use the
count
> command with the following code.

> Sub ChkFirstWhile()
>     counter = 1
>     myNum = 1
>     Do While myNum < 1000
>        Rows ("counter:counter").Select
>     Selection.Insert Shift:=xlDown
>     Selection.Interior.ColorIndex = 15
>     'End With
>         myNum = myNum - 1
>         counter = counter + 1
>     Loop
>     'MsgBox "The loop made " & counter & " repetitions."
> End Sub

> Only problem is that excel does not like my count command.

> Any suggestions?



Mon, 17 Nov 2003 08:03:51 GMT  
 inserting a row to every second line.
Hi, Chris.

How about this?

Sub ChkFirstWhile()
   Dim iRowNo As Integer

   With ActiveSheet
       For iRowNo = 1000 To 1 Step -1
           With .Rows(iRowNo)
               .Insert Shift:=xlDown
               .Interior.ColorIndex = 15
           End With
       Next iRowNo
   End With
End Sub

I hope this helps

Cheers,       Testuya Oguma,  Sydney,  Australia



Mon, 17 Nov 2003 08:12:45 GMT  
 inserting a row to every second line.
That did it.

Thanks heaps as this was a no winner for me.  Brain almost blew up trying to
work it out!  Thanks HEAPS.

Chris.


Quote:
> Hi, Chris.

> How about this?

> Sub ChkFirstWhile()
>    Dim iRowNo As Integer

>    With ActiveSheet
>        For iRowNo = 1000 To 1 Step -1
>            With .Rows(iRowNo)
>                .Insert Shift:=xlDown
>                .Interior.ColorIndex = 15
>            End With
>        Next iRowNo
>    End With
> End Sub

> I hope this helps

> Cheers,       Testuya Oguma,  Sydney,  Australia



Mon, 17 Nov 2003 12:06:55 GMT  
 inserting a row to every second line.
From a strictly academic point of view, it would appear this could not be
done without a programming loop.
So, just for fun...    Dana DeLouis.

Sub InsertRows()
'// Insert Alternating blank rows.
'//  & Color every other Row
'// By: Dana DeLouis

  Dim c As Long
  Const LastRow As Long = 1000

  Application.ScreenUpdating = False
  ActiveSheet.UsedRange
  c = Cells.SpecialCells(xlLastCell)(1, 2).Column

  Cells(1, c) = "TRUE"
  Cells(1, c).Resize(2, 1).AutoFill _
    Destination:=Range(Cells(1, c), Cells(LastRow, c))

  Columns(c).SpecialCells(xlBlanks).EntireRow.Insert
  Cells(1, c).ClearContents

  Columns(c).SpecialCells(xlConstants).EntireRow.Insert
  With Columns(c).SpecialCells(xlConstants)
    Union(Cells(1, c), Cells(3, c), .Offset(2, 0)).Value = True
  End With

  Columns(c).SpecialCells(xlBlanks).EntireRow.Interior.ColorIndex = 15
  Columns(c).ClearContents
  ActiveSheet.UsedRange
  [A1].Select
  Application.ScreenUpdating = True
End Sub


Quote:
> Hi I am trying to insert a new line and gray the (new inserted) line then
> move down and format the next line so that the spreadsheet looks like
every
> second line is greyed and blank.

> I want to apply this to the first 1000 lines and am trying to use the
count
> command with the following code.

> Sub ChkFirstWhile()
>     counter = 1
>     myNum = 1
>     Do While myNum < 1000
>        Rows ("counter:counter").Select
>     Selection.Insert Shift:=xlDown
>     Selection.Interior.ColorIndex = 15
>     'End With
>         myNum = myNum - 1
>         counter = counter + 1
>     Loop
>     'MsgBox "The loop made " & counter & " repetitions."
> End Sub

> Only problem is that excel does not like my count command.

> Any suggestions?



Thu, 20 Nov 2003 00:56:19 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. macro to insert new row every other row

2. Inserting Blank Line Every Other Line...

3. how to delete every second row

4. duplicating every second row

5. Deleting every second row?

6. Please - Macro that deletes every second row

7. re : choose every second row

8. adding every second cell in a row

9. delete every second row

10. Deleting every second row


 
Powered by phpBB® Forum Software © phpBB Group