Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting 3 new rows after the word total
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting 3 new rows after the word total
see response in public.excel
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "dd" wrote in message ups.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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting 3 new rows after the word total
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting 3 new rows after the word total
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 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting 3 new rows after the word total
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 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert a blank row above all cells in column a:a that have a given value ?
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 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting 3 new rows after the word total
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 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a row below last row of data to Sum total | Excel Discussion (Misc queries) | |||
Inserting rows and adding to total worksheet | Excel Discussion (Misc queries) | |||
Insert Rows after word total | Excel Programming | |||
inserting rows at common word in a sheet | Excel Discussion (Misc queries) | |||
Change Word Total and Grand Total | Excel Programming |