Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I insert a true blank inst. of a non-blank zero string | Excel Worksheet Functions | |||
Insert a BLANK value | Excel Worksheet Functions | |||
Insert Text When Blank | Excel Discussion (Misc queries) | |||
Insert A blank Row | Excel Programming | |||
Insert blank row | Excel Programming |