Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert A blank Row
I used the Insert a blank column function and it works
great, I was trying to work out how to modify it to insert a blank row, in column B ther will be the word line, if it finds that word I want it to insert a blank row after that and then continue on to a maximum of x number of rows Any Ideas Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert A blank Row
Hi Nigel
Example for column A Sub test2() ' below the word Dim Rng As Range Dim findstring As String findstring = "line" Set Rng = Range("A:A").Find(What:=findstring, After:=Range("A65536"), LookAt:=xlWhole) While Not Rng Is Nothing Rng.Offset(1, 0).EntireRow.Insert Set Rng = Range("A" & Rng.Row + 1 & ":A" & Rows.Count) _ .Find(What:=findstring, After:=Range("A65536"), LookAt:=xlWhole) Wend End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Nigel Bennett" wrote in message ... I used the Insert a blank column function and it works great, I was trying to work out how to modify it to insert a blank row, in column B ther will be the word line, if it finds that word I want it to insert a blank row after that and then continue on to a maximum of x number of rows Any Ideas Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert A blank Row
Here is my kick at it... Very similar to Ron's... You can define the sheet to
search Set wks = ActiveSheet (or whatever sheet you want) and the range to search Set rngToSearch = wks.Range("B2:B10") 'Or whatever range you want.... Public Sub InsertRows() Dim wks As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Set wks = ActiveSheet Set rngToSearch = wks.Range("B2:B10") Set rngFound = rngToSearch.Find("Line", , xlValues, xlPart) If Not rngFound Is Nothing Then Set rngFirst = rngFound rngFound.EntireRow.Insert (xlDown) Do Set rngFound = rngToSearch.FindNext(rngFound) rngFound.EntireRow.Insert (xlDown) Loop Until rngFound.Address = rngFirst.Address End If End Sub HTH "Nigel Bennett" wrote: I used the Insert a blank column function and it works great, I was trying to work out how to modify it to insert a blank row, in column B ther will be the word line, if it finds that word I want it to insert a blank row after that and then continue on to a maximum of x number of rows Any Ideas Thanks |
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 Blank Row Below Sub-Total? | Excel Worksheet Functions | |||
Insert a BLANK value | Excel Worksheet Functions | |||
Macro To Insert Blank Row? | Excel Discussion (Misc queries) | |||
Insert blank row | Excel Programming |