Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row after blank cell encountered
I have a large table with infrequent blank cells in a single column. I want
to located these and insert a new row directly after that blank cell's row. This isn't working .... Sub InsertBlankLine() ' FinalRow = Cells(Rows.Count, 8).End(xlUp).Row ' For Counter = 1 To FinalRow Set curCell = Worksheets("Sheet1").Cells(Counter, 6) If curCell.Value < 1 Then curCell.Value = FinalRow ' ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown ' Next Counter End Sub Row 8 contains the first blank cell condition but the macro inserts 13 blank rows right after the first row. (13 is the value of FinalRow). I have spent 4 hours on this trying every combination I can think of. Your help is appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row after blank cell encountered
Hi Don,
I acquire the total rows count fine and place the value in the blank cells when encountered. That worked fine. The problem began when I attempted to add code to insert a blank line directly after encountering the blank cell. (By posting the "FinalRow" value in the blank cell, it gave me confirmation I was looping through the data set properly). As I said, it was only when I started using the "Insert row" code that I had trouble. I understand that working from the bottom up can sometimes be helpful but I don't see an advantage in this case and, it is easier conceptually for me to work top down. I won't take you up on your offer though I greatly appreciate it. Best regards, Ken "Don Guillett" wrote: Best to work from the bottom up I can't figure out what you are trying to do for i= cells(rows.count,8).end(xlup).row to 2 step -1 If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You send a clear explanation of what you want 3. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "KG Old Wolf" wrote in message ... I have a large table with infrequent blank cells in a single column. I want to located these and insert a new row directly after that blank cell's row. This isn't working .... Sub InsertBlankLine() ' FinalRow = Cells(Rows.Count, 8).End(xlUp).Row ' For Counter = 1 To FinalRow Set curCell = Worksheets("Sheet1").Cells(Counter, 6) If curCell.Value < 1 Then curCell.Value = FinalRow ' ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown ' Next Counter End Sub Row 8 contains the first blank cell condition but the macro inserts 13 blank rows right after the first row. (13 is the value of FinalRow). I have spent 4 hours on this trying every combination I can think of. Your help is appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row after blank cell encountered
Sub insertblanklineSAS()
finalrow = Cells(Rows.Count, 8).End(xlUp).Row For i = finalrow To 1 Step -1 If Len(Application.Trim(Cells(i, 6))) < 1 Then Rows(i + 1).Insert End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "KG Old Wolf" wrote in message ... I have a large table with infrequent blank cells in a single column. I want to located these and insert a new row directly after that blank cell's row. This isn't working .... Sub InsertBlankLine() ' FinalRow = Cells(Rows.Count, 8).End(xlUp).Row ' For Counter = 1 To FinalRow Set curCell = Worksheets("Sheet1").Cells(Counter, 6) If curCell.Value < 1 Then curCell.Value = FinalRow ' ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown ' Next Counter End Sub Row 8 contains the first blank cell condition but the macro inserts 13 blank rows right after the first row. (13 is the value of FinalRow). I have spent 4 hours on this trying every combination I can think of. Your help is appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row after blank cell encountered
I owe you one Don - you taught a few lessons here.... thank you
Lesson 1 - how to insert the rows -- Yes, IT WORKED! Lesson 2 - LISTEN TO OTHERS.... you said to go from bottom up, I chose not to - couldn't get it to work... you did. Thank you very much, Ken "Don Guillett" wrote: Sub insertblanklineSAS() finalrow = Cells(Rows.Count, 8).End(xlUp).Row For i = finalrow To 1 Step -1 If Len(Application.Trim(Cells(i, 6))) < 1 Then Rows(i + 1).Insert End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "KG Old Wolf" wrote in message ... I have a large table with infrequent blank cells in a single column. I want to located these and insert a new row directly after that blank cell's row. This isn't working .... Sub InsertBlankLine() ' FinalRow = Cells(Rows.Count, 8).End(xlUp).Row ' For Counter = 1 To FinalRow Set curCell = Worksheets("Sheet1").Cells(Counter, 6) If curCell.Value < 1 Then curCell.Value = FinalRow ' ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown ' Next Counter End Sub Row 8 contains the first blank cell condition but the macro inserts 13 blank rows right after the first row. (13 is the value of FinalRow). I have spent 4 hours on this trying every combination I can think of. Your help is appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row after blank cell encountered
If you are interested, take a look at the solution I posted for a
non-looping method of doing the insertions. -- Rick (MVP - Excel) "KG Old Wolf" wrote in message ... I owe you one Don - you taught a few lessons here.... thank you Lesson 1 - how to insert the rows -- Yes, IT WORKED! Lesson 2 - LISTEN TO OTHERS.... you said to go from bottom up, I chose not to - couldn't get it to work... you did. Thank you very much, Ken "Don Guillett" wrote: Sub insertblanklineSAS() finalrow = Cells(Rows.Count, 8).End(xlUp).Row For i = finalrow To 1 Step -1 If Len(Application.Trim(Cells(i, 6))) < 1 Then Rows(i + 1).Insert End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "KG Old Wolf" wrote in message ... I have a large table with infrequent blank cells in a single column. I want to located these and insert a new row directly after that blank cell's row. This isn't working .... Sub InsertBlankLine() ' FinalRow = Cells(Rows.Count, 8).End(xlUp).Row ' For Counter = 1 To FinalRow Set curCell = Worksheets("Sheet1").Cells(Counter, 6) If curCell.Value < 1 Then curCell.Value = FinalRow ' ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown ' Next Counter End Sub Row 8 contains the first blank cell condition but the macro inserts 13 blank rows right after the first row. (13 is the value of FinalRow). I have spent 4 hours on this trying every combination I can think of. Your help is appreciated. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row after blank cell encountered
If I understand your question correctly (all you want to do is insert the
blank lines, not assign the FinalRow value to the blank cells), then this code should do what you want... Sub InsertBlankLines() Dim FinalRow As Long With Worksheets("Sheet1") On Error Resume Next FinalRow = .Cells(.Rows.Count, 8).End(xlUp).Row .Range("F1:F" & FinalRow).SpecialCells(xlCellTypeBlanks). _ Offset(1).EntireRow.Insert End With End Sub I'd advise that you copy/paste the above, but if you decide to type it in instead, then pay close attention to the leading dots that I have used (they make the range refer back to the object of the With statement (Sheet1 in this case). -- Rick (MVP - Excel) "KG Old Wolf" wrote in message ... I have a large table with infrequent blank cells in a single column. I want to located these and insert a new row directly after that blank cell's row. This isn't working .... Sub InsertBlankLine() ' FinalRow = Cells(Rows.Count, 8).End(xlUp).Row ' For Counter = 1 To FinalRow Set curCell = Worksheets("Sheet1").Cells(Counter, 6) If curCell.Value < 1 Then curCell.Value = FinalRow ' ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown ' Next Counter End Sub Row 8 contains the first blank cell condition but the macro inserts 13 blank rows right after the first row. (13 is the value of FinalRow). I have spent 4 hours on this trying every combination I can think of. Your help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Up until Non-Blank Cell encountered | Excel Discussion (Misc queries) | |||
if cell is blank insert a zero | Excel Discussion (Misc queries) | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
when encountered Zero/Blank CF issues ... | Excel Programming | |||
Copy value and fill blank spaces below it until new value is encountered? | Excel Programming |