Insert a blank row (2)
 

Dear Ron,
Your code a while earlier works beautifully only if "Total" appears.
My Totals are created by a recorded macro DataSubtotal and is therefore
"......Total".
How should the resulting code be modified to (1) (if possible) to
bold the entire Total Line (now only the break identifier is Bold),
(2) Insert a blank row after each line having the "........... Total").


Thank you.
--
Robert

Insert a blank row (2)
 

Hi Robert

Untested but try this one
I use xlPart now

Sub test2()
' above the word
Dim Rng As Range
Dim findstring As String
findstring = "Total"
Set Rng = Range("A:A").Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
While Not Rng Is Nothing
Rng.EntireRow.Cells.Font.Bold = True
Rng.EntireRow.Insert
Set Rng = Range("A" & Rng.Row + 1 & ":A" & Rows.Count) _
.Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Robert" wrote in message ...
Dear Ron,
Your code a while earlier works beautifully only if "Total" appears.
My Totals are created by a recorded macro DataSubtotal and is therefore
"......Total".
How should the resulting code be modified to (1) (if possible) to
bold the entire Total Line (now only the break identifier is Bold),
(2) Insert a blank row after each line having the "........... Total").


Thank you.
--
Robert

Insert a blank row (2)
 

Hi Robert

Untested but try this one
I use xlPart now

Sub test2()
Dim Rng As Range
Dim findstring As String
findstring = "Total"
Set Rng = Range("A:A").Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
While Not Rng Is Nothing
Rng.EntireRow.Cells.Font.Bold = True
Rng.EntireRow.Insert
Set Rng = Range("A" & Rng.Row + 1 & ":A" & Rows.Count) _
.Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Robert" wrote in message ...
Dear Ron,
Your code a while earlier works beautifully only if "Total" appears.
My Totals are created by a recorded macro DataSubtotal and is therefore
"......Total".
How should the resulting code be modified to (1) (if possible) to
bold the entire Total Line (now only the break identifier is Bold),
(2) Insert a blank row after each line having the "........... Total").


Thank you.
--
Robert

Insert a blank row (2)
 

Ron, Thank you very much. Your second code works but the row
insert takes place above the "Total" row. The row insert should be
below ie after ".Total" row. I shall await eagerly for your amendment.

Robert
"Ron de Bruin" wrote:

Hi Robert

Untested but try this one
I use xlPart now

Sub test2()
Dim Rng As Range
Dim findstring As String
findstring = "Total"
Set Rng = Range("A:A").Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
While Not Rng Is Nothing
Rng.EntireRow.Cells.Font.Bold = True
Rng.EntireRow.Insert
Set Rng = Range("A" & Rng.Row + 1 & ":A" & Rows.Count) _
.Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Robert" wrote in message ...
Dear Ron,
Your code a while earlier works beautifully only if "Total" appears.
My Totals are created by a recorded macro DataSubtotal and is therefore
"......Total".
How should the resulting code be modified to (1) (if possible) to
bold the entire Total Line (now only the break identifier is Bold),
(2) Insert a blank row after each line having the "........... Total").


Thank you.
--
Robert

Insert a blank row (2)
 

Dear Ron, Further to my last posting, I solved the problem by copying from
your original post and replacing

" Rng.EntireRow.Insert"
with
"Rng.(Offset(1, 0)EntireRow.Insert"

Thank you again Ron, I am so happy with this.
RobertR

Insert a blank row (2)
 

"Rng.(Offset(1, 0)EntireRow.Insert"

Sorry, for using the wrong example

Thank you again Ron, I am so happy with this.
You are welcome


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Robert" wrote in message ...
Dear Ron, Further to my last posting, I solved the problem by copying from
your original post and replacing

" Rng.EntireRow.Insert"
with
"Rng.(Offset(1, 0)EntireRow.Insert"

Thank you again Ron, I am so happy with this.
RobertR

Insert a blank row (2)
 

Ron,
I was actually referred to you by someone else in the discussion group. I
am trying to do the "insert row" function in my spreadsheet as well. Forgive
my ignorance, but I don't quite understand what and how these macros work
yet. If you have a moment, could you please respond and just tell me the
exact steps I need to achieve inserting rows? Do I need to record a macro,
create a macro, be on a certain cell in the spreadsheet??? Your help is
greatly appreciated.
Regards,
Rager

"Ron de Bruin" wrote:

Hi Robert

Untested but try this one
I use xlPart now

Sub test2()
Dim Rng As Range
Dim findstring As String
findstring = "Total"
Set Rng = Range("A:A").Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
While Not Rng Is Nothing
Rng.EntireRow.Cells.Font.Bold = True
Rng.EntireRow.Insert
Set Rng = Range("A" & Rng.Row + 1 & ":A" & Rows.Count) _
.Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Robert" wrote in message ...
Dear Ron,
Your code a while earlier works beautifully only if "Total" appears.
My Totals are created by a recorded macro DataSubtotal and is therefore
"......Total".
How should the resulting code be modified to (1) (if possible) to
bold the entire Total Line (now only the break identifier is Bold),
(2) Insert a blank row after each line having the "........... Total").


Thank you.
--
Robert

Insert a blank row (2)
 

Hi Rager

I which sheet(name) and column is your data
Above or below which word you want to insert a row

If you give me the answers I will help you


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rager" wrote in message ...
Ron,
I was actually referred to you by someone else in the discussion group. I
am trying to do the "insert row" function in my spreadsheet as well. Forgive
my ignorance, but I don't quite understand what and how these macros work
yet. If you have a moment, could you please respond and just tell me the
exact steps I need to achieve inserting rows? Do I need to record a macro,
create a macro, be on a certain cell in the spreadsheet??? Your help is
greatly appreciated.
Regards,
Rager

"Ron de Bruin" wrote:

Hi Robert

Untested but try this one
I use xlPart now

Sub test2()
Dim Rng As Range
Dim findstring As String
findstring = "Total"
Set Rng = Range("A:A").Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
While Not Rng Is Nothing
Rng.EntireRow.Cells.Font.Bold = True
Rng.EntireRow.Insert
Set Rng = Range("A" & Rng.Row + 1 & ":A" & Rows.Count) _
.Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Robert" wrote in message ...
Dear Ron,
Your code a while earlier works beautifully only if "Total" appears.
My Totals are created by a recorded macro DataSubtotal and is therefore
"......Total".
How should the resulting code be modified to (1) (if possible) to
bold the entire Total Line (now only the break identifier is Bold),
(2) Insert a blank row after each line having the "........... Total").


Thank you.
--
Robert

Insert a blank row (2)
 

Ron,
I have 12 different worksheets (January through December). Below is
basically what every worksheet looks like, except I have 8 different sections
like this and the subtotal row and column D are highlighted. I would like to
be able to insert a row above the subtotal row if there is a need to do so.
If you need any further information, just let me know! Thank you so very
much for your help!


A...............B................C................ ............D
8 name cost cost Difference
9 same
10 same
11 same
12 same
13 same
14subtotal =SUM(B17:B21)=SUM(C17:C21) =B22-C22

A...............B................C................ ............D
16
17
18
19
2
21subtotal........................................ ...............................




"Ron de Bruin" wrote:

Hi Rager

I which sheet(name) and column is your data
Above or below which word you want to insert a row

If you give me the answers I will help you


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rager" wrote in message ...
Ron,
I was actually referred to you by someone else in the discussion group. I
am trying to do the "insert row" function in my spreadsheet as well. Forgive
my ignorance, but I don't quite understand what and how these macros work
yet. If you have a moment, could you please respond and just tell me the
exact steps I need to achieve inserting rows? Do I need to record a macro,
create a macro, be on a certain cell in the spreadsheet??? Your help is
greatly appreciated.
Regards,
Rager

"Ron de Bruin" wrote:

Hi Robert

Untested but try this one
I use xlPart now

Sub test2()
Dim Rng As Range
Dim findstring As String
findstring = "Total"
Set Rng = Range("A:A").Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
While Not Rng Is Nothing
Rng.EntireRow.Cells.Font.Bold = True
Rng.EntireRow.Insert
Set Rng = Range("A" & Rng.Row + 1 & ":A" & Rows.Count) _
.Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Robert" wrote in message ...
Dear Ron,
Your code a while earlier works beautifully only if "Total" appears.
My Totals are created by a recorded macro DataSubtotal and is therefore
"......Total".
How should the resulting code be modified to (1) (if possible) to
bold the entire Total Line (now only the break identifier is Bold),
(2) Insert a blank row after each line having the "........... Total").


Thank you.
--
Robert

Insert a blank row (2)
 

Hi

Try this

This macro is working for the activesheet and insert a row above every cell with
the word subtotal in column A.

Alt-F11 to open the VBA editor
Insert module from the menubar
Paste the sub
Alt-Q to go back to excel

Alt-F8
Select test
Run

Sub test()
Dim Rng As Range
Dim findstring As String
findstring = "subtotal"
Set Rng = Range("A:A").Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
While Not Rng Is Nothing
'Rng.EntireRow.Cells.Font.Bold = True
Rng.EntireRow.Insert
Set Rng = Range("A" & Rng.Row + 1 & ":A" & Rows.Count) _
.Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rager" wrote in message ...
Ron,
I have 12 different worksheets (January through December). Below is
basically what every worksheet looks like, except I have 8 different sections
like this and the subtotal row and column D are highlighted. I would like to
be able to insert a row above the subtotal row if there is a need to do so.
If you need any further information, just let me know! Thank you so very
much for your help!


A...............B................C................ ............D
8 name cost cost Difference
9 same
10 same
11 same
12 same
13 same
14subtotal =SUM(B17:B21)=SUM(C17:C21) =B22-C22

A...............B................C................ ............D
16
17
18
19
20
21subtotal........................................ ...............................




"Ron de Bruin" wrote:

Hi Rager

I which sheet(name) and column is your data
Above or below which word you want to insert a row

If you give me the answers I will help you


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rager" wrote in message ...
Ron,
I was actually referred to you by someone else in the discussion group. I
am trying to do the "insert row" function in my spreadsheet as well. Forgive
my ignorance, but I don't quite understand what and how these macros work
yet. If you have a moment, could you please respond and just tell me the
exact steps I need to achieve inserting rows? Do I need to record a macro,
create a macro, be on a certain cell in the spreadsheet??? Your help is
greatly appreciated.
Regards,
Rager

"Ron de Bruin" wrote:

Hi Robert

Untested but try this one
I use xlPart now

Sub test2()
Dim Rng As Range
Dim findstring As String
findstring = "Total"
Set Rng = Range("A:A").Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
While Not Rng Is Nothing
Rng.EntireRow.Cells.Font.Bold = True
Rng.EntireRow.Insert
Set Rng = Range("A" & Rng.Row + 1 & ":A" & Rows.Count) _
.Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Robert" wrote in message ...
Dear Ron,
Your code a while earlier works beautifully only if "Total" appears.
My Totals are created by a recorded macro DataSubtotal and is therefore
"......Total".
How should the resulting code be modified to (1) (if possible) to
bold the entire Total Line (now only the break identifier is Bold),
(2) Insert a blank row after each line having the "........... Total").


Thank you.
--
Robert

Insert a blank row (2)
 

WORKS GREAT!!! THANK YOU SO VERY MUCH RON!

"Ron de Bruin" wrote:

Hi

Try this

This macro is working for the activesheet and insert a row above every cell with
the word subtotal in column A.

Alt-F11 to open the VBA editor
Insert module from the menubar
Paste the sub
Alt-Q to go back to excel

Alt-F8
Select test
Run

Sub test()
Dim Rng As Range
Dim findstring As String
findstring = "subtotal"
Set Rng = Range("A:A").Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
While Not Rng Is Nothing
'Rng.EntireRow.Cells.Font.Bold = True
Rng.EntireRow.Insert
Set Rng = Range("A" & Rng.Row + 1 & ":A" & Rows.Count) _
.Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rager" wrote in message ...
Ron,
I have 12 different worksheets (January through December). Below is
basically what every worksheet looks like, except I have 8 different sections
like this and the subtotal row and column D are highlighted. I would like to
be able to insert a row above the subtotal row if there is a need to do so.
If you need any further information, just let me know! Thank you so very
much for your help!


A...............B................C................ ............D
8 name cost cost Difference
9 same
10 same
11 same
12 same
13 same
14subtotal =SUM(B17:B21)=SUM(C17:C21) =B22-C22

A...............B................C................ ............D
16
17
18
19
20
21subtotal........................................ ...............................




"Ron de Bruin" wrote:

Hi Rager

I which sheet(name) and column is your data
Above or below which word you want to insert a row

If you give me the answers I will help you


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rager" wrote in message ...
Ron,
I was actually referred to you by someone else in the discussion group. I
am trying to do the "insert row" function in my spreadsheet as well. Forgive
my ignorance, but I don't quite understand what and how these macros work
yet. If you have a moment, could you please respond and just tell me the
exact steps I need to achieve inserting rows? Do I need to record a macro,
create a macro, be on a certain cell in the spreadsheet??? Your help is
greatly appreciated.
Regards,
Rager

"Ron de Bruin" wrote:

Hi Robert

Untested but try this one
I use xlPart now

Sub test2()
Dim Rng As Range
Dim findstring As String
findstring = "Total"
Set Rng = Range("A:A").Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
While Not Rng Is Nothing
Rng.EntireRow.Cells.Font.Bold = True
Rng.EntireRow.Insert
Set Rng = Range("A" & Rng.Row + 1 & ":A" & Rows.Count) _
.Find(What:=findstring, After:=Range("A" & Rows.Count), LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Robert" wrote in message ...
Dear Ron,
Your code a while earlier works beautifully only if "Total" appears.
My Totals are created by a recorded macro DataSubtotal and is therefore
"......Total".
How should the resulting code be modified to (1) (if possible) to
bold the entire Total Line (now only the break identifier is Bold),
(2) Insert a blank row after each line having the "........... Total").


Thank you.
--
Robert