Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help With Insert Row Macro
I need some help with a macro to insert one row at a point where there is text in a particular column. The worksheet has multiple columns, but the two relevant ones are headed Category (Column A) and Tasks (Column D). Each Category description has at least one task, but can have up to 20. I have been using the macro below to insert one row before each new category. However, what I have discovered is that this is actual inserting one row after the category description. This is fine as long as there is only ever one task, but this is not the case. Everything that I have tried so far seems to only cause the macro to go into a loop Any input / help would be appreciated Thanks Karen Sub InsertRows() With Range("A10:A2498") On Error Resume Next Set C = .Find(What:="*", LookIn:=xlValues) If Not C Is Nothing Then firstAddress = C.Address Do C.Offset(1, 0).EntireRow.Insert Set C = .FindNext(C) Loop While Not C Is Nothing And C.Address < firstAddress End If End With End Sub -- ksp ------------------------------------------------------------------------ ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267 View this thread: http://www.excelforum.com/showthread...hreadid=502787 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help With Insert Row Macro
Hi Karen,
The reason you were getting stuck in the loop is the loop while check was looking for something in the firstAddress in which you found text. But once you found it the first time, you shift it down, so the loop will never find anything in that cell again. Try the following: Sub InsertRows() With Range("A10:A2498") On Error Resume Next Set C = .Find(What:="*", LookIn:=xlValues) If Not C Is Nothing Then firstaddress = C.Offset(1, 0).Address Do C.Offset(-1, 0).EntireRow.Insert Set C = .FindNext(C) Loop While Not C Is Nothing And C.Address firstaddress End If End With End Sub Hope this works for you, Anita |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help With Insert Row Macro
Hi Anita It sort of worked. I am no longer getting the endless loop, but I suspect that it is still looping as it is now inserting 6 rows between each - any ideas why? Thanks Karen -- ksp ------------------------------------------------------------------------ ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267 View this thread: http://www.excelforum.com/showthread...hreadid=502787 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help With Insert Row Macro
Oops. On inserting the row I was using -1 rows when I shouldn't have
been offsetting it at all. Here's the Do Loop with a change to line 2. Do c.EntireRow.Insert Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address firstaddress |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help With Insert Row Macro
Hi Anita Thought you might like to know that I played around with the changes you suggested (see below) and it now works perfectly - thanks heaps for your input Karen Sub InsertRows() With Range("A10:A2499") On Error Resume Next Set C = .Find(What:="*", LookIn:=xlValues) If Not C Is Nothing Then firstaddress = C.Offset(1, 0).Address Do C.Offset(0, 0).EntireRow.Insert Set C = .FindNext(C) Loop While Not C Is Nothing And C.Address < firstaddress End If End With End Sub -- ksp ------------------------------------------------------------------------ ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267 View this thread: http://www.excelforum.com/showthread...hreadid=502787 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help With Insert Row Macro
Thanks so much for letting me know that it worked out for you!
Anita |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to insert a row after every 4th row | Excel Discussion (Misc queries) | |||
Macro to Insert Current Date into cell - Macro to "Save As" | Excel Worksheet Functions | |||
make a macro to insert a macro | Excel Discussion (Misc queries) | |||
Insert Row Macro Help | Excel Programming | |||
Insert A Row Macro | New Users to Excel |