PDA

View Full Version : [SOLVED] Insert Macro



FusionRed
01-19-2005, 02:31 PM
Hello Everyone:

I'm a newbie to VBA and having trouble with what I think should be a very easy macro. I'm trying to search a range for all yellow lines beginning from E11 to the end of the data in the spreadsheet. I want to add a blank line above and below the yellow line and both lines should have no fill color.

The following is some code I've tried (just a version of many tries):


Sub Insert_Blank_Rows()
' Insert_Blank_Rows before and after total line in Elabor Budgets
' Macro recorded 1/19/2005 by Pitney Bowes Inc.
Dim Cell As Range
Dim r As Range
Set r = Range("E11", ActiveCell.SpecialCells(xlCellTypeLastCell))
For Each Cell In r
Select Case Cell.Interior.ColorIndex
Case Cell.Interior.ColorIndex = 36
Selection.EntireRow.Insert
End Select
Next
End Sub

Can someone out there help me here????

Many thanks for your input and expertise.

FusionRed:devil:

Zack Barresse
01-19-2005, 04:45 PM
Hi FusionRed! Welcome to VBAX! :hi:


I enclosed your code with our [ VBA ] tags (courtesy of Mark007!) to make your code look that way. I also moved the thread to the Excel Help forum.

Maybe you could do something like this ...


Sub Insert_Blank_Rows()
' Insert_Blank_Rows before and after total line in Elabor Budgets
' Macro recorded 1/19/2005 by Pitney Bowes Inc.
Dim Cell As Range
Dim r As Range
Set r = Range("E11", Range("E65536").End(xlUp))
For Each Cell In r
If Cell.Interior.ColorIndex = 36 Then
Cell.Offset(1).Interior.ColorIndex = 0
Cell.Offset(1).ClearContents
Cell.Offset(-1).Interior.ColorIndex = 0
Cell.Offset(-1).ClearContents
End If
Next
End Sub

If I understood you correctly.
Let me know how it works.

FusionRed
01-20-2005, 07:13 AM
Thanks for the help. Unfortunately, no lines are inserted when I run the code, in fact nothing changes on the spreadsheet.

So, I note your code doesn't use select row or insert commands. To help my understanding of VBA let me pose a question, when using the cell.offset(1) command does that automatically select the row above and do the insert???? And the ClearContents command is that done for good form?

Thanks again for all of your time and expertise.

FusionRed

Zack Barresse
01-20-2005, 10:29 AM
Oh, sorry, I misunderstood. I thought you just wanted to clear the row of the cell above and below the cell with yellow highlight.

As far as the select goes, you don't need to Select anything really. The Offset will handle that as we iterate through the range. Try this ...


Option Explicit

Sub Insert_Blank_Rows()
' Insert_Blank_Rows before and after total line in Elabor Budgets
' Macro recorded 1/19/2005 by Pitney Bowes Inc.
Dim r As Range, i As Long
Set r = Range("E11", Range("E65536").End(xlUp))
For i = r.Cells.Count To 1 Step -1
If r.Cells(i).Interior.ColorIndex = 36 Then
r.Cells(i).EntireRow.Insert
i = i + 1
r.Cells(i).Offset(1).EntireRow.Insert
r.Cells(i).Offset(1).EntireRow.Interior.ColorIndex = 0
End If
Next i
End Sub

FusionRed
01-20-2005, 12:29 PM
Many thanks to everyone, this works like a charm.

FusionRed

Zack Barresse
01-20-2005, 12:41 PM
Great! I'll mark this thread Solved then. You can do so on your own threads by going to Thread Tools --> Mark Solved --> Perform Action.

Take care! :yes