[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Inserting 3 new rows after the word total

dd

12/16/2006 12:03:00 AM

Hi, I'm working with multiple rows of data, probably around 3000 rows
and I have the word "total" in between the data in Column A in Excel. I

want to add three blank rows after the word Total. The word Total is
also in bold font. Can someone please help with a macro? Thanks.
6 Answers

Bob Phillips

12/16/2006 12:39:00 AM

0

see response in public.excel

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dd" <dd2quest@gmail.com> wrote in message
news:1166227367.067440.221440@f1g2000cwa.googlegroups.com...
> Hi, I'm working with multiple rows of data, probably around 3000 rows
> and I have the word "total" in between the data in Column A in Excel. I
>
> want to add three blank rows after the word Total. The word Total is
> also in bold font. Can someone please help with a macro? Thanks.
>


Martin Fishlock

12/16/2006 1:03:00 AM

0

dd, I think this will do your job, assuming the items are in column A.

Sub insert3rows()

Dim i As Integer
Dim rCell As Range
Dim lFirstRow As Long
With ActiveSheet.Range("A:A")
Set rCell = .Find("total", _
LookIn:=xlValues, LookAt:=xlWhole)
If Not rCell Is Nothing Then
lFirstRow = rCell.Row
Do
With rCell.Offset(1, 0)
For i = 1 To 3
.Insert xlShiftDown
Next i
End With
Set rCell = .FindNext(rCell)
Loop While Not rCell Is Nothing And rCell.Row <> lFirstRow
End If
End With

End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"dd" wrote:

> Hi, I'm working with multiple rows of data, probably around 3000 rows
> and I have the word "total" in between the data in Column A in Excel. I
>
> want to add three blank rows after the word Total. The word Total is
> also in bold font. Can someone please help with a macro? Thanks.
>
>

dd

12/16/2006 9:49:00 PM

0

Thanks Martin.
I'll test this next week but it seems to work fine for now.
I also rated this 5 stars.
Thanks again.


On Dec 15, 5:03 pm, Martin Fishlock
<martin_fishl...@yahoo.co.uk.cutthis> wrote:
> dd, I think this will do your job, assuming the items are in column A.
>
> Sub insert3rows()
>
> Dim i As Integer
> Dim rCell As Range
> Dim lFirstRow As Long
> With ActiveSheet.Range("A:A")
> Set rCell = .Find("total", _
> LookIn:=xlValues, LookAt:=xlWhole)
> If Not rCell Is Nothing Then
> lFirstRow = rCell.Row
> Do
> With rCell.Offset(1, 0)
> For i = 1 To 3
> .Insert xlShiftDown
> Next i
> End With
> Set rCell = .FindNext(rCell)
> Loop While Not rCell Is Nothing And rCell.Row <> lFirstRow
> End If
> End With
>
> End Sub
>
> --
> Hope this helps
> Martin Fishlock
> Please do not forget to rate this reply.
>
> "dd" wrote:
> > Hi, I'm working with multiple rows of data, probably around 3000 rows
> > and I have the word "total" in between the data in Column A in Excel. I
>
> > want to add three blank rows after the word Total. The word Total is
> > also in bold font. Can someone please help with a macro? Thanks.

Snuffwinkler

12/17/2006 1:07:00 PM

0


dd wrote:

> Thanks Martin.
> I'll test this next week but it seems to work fine for now.
> I also rated this 5 stars.
> Thanks again.
>
>
> On Dec 15, 5:03 pm, Martin Fishlock
> <martin_fishl...@yahoo.co.uk.cutthis> wrote:
> > dd, I think this will do your job, assuming the items are in column A.
> >
> > Sub insert3rows()
> >
> > Dim i As Integer
> > Dim rCell As Range
> > Dim lFirstRow As Long
> > With ActiveSheet.Range("A:A")
> > Set rCell = .Find("total", _
> > LookIn:=xlValues, LookAt:=xlWhole)
> > If Not rCell Is Nothing Then
> > lFirstRow = rCell.Row
> > Do
> > With rCell.Offset(1, 0)
> > For i = 1 To 3
> > .Insert xlShiftDown
> > Next i
> > End With
> > Set rCell = .FindNext(rCell)
> > Loop While Not rCell Is Nothing And rCell.Row <> lFirstRow
> > End If
> > End With
> >
> > End Sub
> >
> > --
> > Hope this helps
> > Martin Fishlock
> > Please do not forget to rate this reply.
> >
> > "dd" wrote:
> > > Hi, I'm working with multiple rows of data, probably around 3000 rows
> > > and I have the word "total" in between the data in Column A in Excel. I
> >
> > > want to add three blank rows after the word Total. The word Total is
> > > also in bold font. Can someone please help with a macro? Thanks.

Snuffwinkler

12/17/2006 1:22:00 PM

0

I found this solution helpful but have been trying to conditionally
format a worksheet based on values in different cells.
The thing I haven't been able to do is insert a row above cells of a
given value without getting into a long/infinite loop.
A solution similar to the one below but inserting above the cell would
be brilliant - can anyone help pls.

dd wrote:

> Thanks Martin.
> I'll test this next week but it seems to work fine for now.
> I also rated this 5 stars.
> Thanks again.
>
>
> On Dec 15, 5:03 pm, Martin Fishlock
> <martin_fishl...@yahoo.co.uk.cutthis> wrote:
> > dd, I think this will do your job, assuming the items are in column A.
> >
> > Sub insert3rows()
> >
> > Dim i As Integer
> > Dim rCell As Range
> > Dim lFirstRow As Long
> > With ActiveSheet.Range("A:A")
> > Set rCell = .Find("total", _
> > LookIn:=xlValues, LookAt:=xlWhole)
> > If Not rCell Is Nothing Then
> > lFirstRow = rCell.Row
> > Do
> > With rCell.Offset(1, 0)
> > For i = 1 To 3
> > .Insert xlShiftDown
> > Next i
> > End With
> > Set rCell = .FindNext(rCell)
> > Loop While Not rCell Is Nothing And rCell.Row <> lFirstRow
> > End If
> > End With
> >
> > End Sub
> >
> > --
> > Hope this helps
> > Martin Fishlock
> > Please do not forget to rate this reply.
> >
> > "dd" wrote:
> > > Hi, I'm working with multiple rows of data, probably around 3000 rows
> > > and I have the word "total" in between the data in Column A in Excel. I
> >
> > > want to add three blank rows after the word Total. The word Total is
> > > also in bold font. Can someone please help with a macro? Thanks.

dd

12/18/2006 7:54:00 PM

0

Hi,
I test this today and it did not work on my data. Probably because I
used the Data->Subtotal function in Excel to get the total. This macro
did not insert any rows. But when I opened a new worksheet and manually
typed in a few rows of data in Col A with the word total, it did insert
3 new rows after total. How would I insert 3 new rows after using the
Subtotal function? Thanks



On Dec 15, 5:03 pm, Martin Fishlock
<martin_fishl...@yahoo.co.uk.cutthis> wrote:
> dd, I think this will do your job, assuming the items are in column A.
>
> Sub insert3rows()
>
> Dim i As Integer
> Dim rCell As Range
> Dim lFirstRow As Long
> With ActiveSheet.Range("A:A")
> Set rCell = .Find("total", _
> LookIn:=xlValues, LookAt:=xlWhole)
> If Not rCell Is Nothing Then
> lFirstRow = rCell.Row
> Do
> With rCell.Offset(1, 0)
> For i = 1 To 3
> .Insert xlShiftDown
> Next i
> End With
> Set rCell = .FindNext(rCell)
> Loop While Not rCell Is Nothing And rCell.Row <> lFirstRow
> End If
> End With
>
> End Sub
>
> --
> Hope this helps
> Martin Fishlock
> Please do not forget to rate this reply.
>
>
>
> "dd" wrote:
> > Hi, I'm working with multiple rows of data, probably around 3000 rows
> > and I have the word "total" in between the data in Column A in Excel. I
>
> > want to add three blank rows after the word Total. The word Total is
> > also in bold font. Can someone please help with a macro? Thanks.- Hide quoted text -- Show quoted text -