PDA

View Full Version : Insert Rows



talytech
10-26-2009, 01:29 PM
Can someone help me with code to insert rows for a worksheet.

Here's what i'm trying to do. I want to loop through the rows in column A until the last row. if there are no commas in the value move to the next row, if there are 2 commas in the value insert 2 rows below, if there is 1 comma in the value then insert 1 row below.

I've attached a sample of the file. For example row A1, the code should just move to the next row .. rowA2 ... move to the next row, row A3 insert 2 rows below and so forth and so on.

Hope someone could help. Thanks

stanleydgrom
10-26-2009, 04:20 PM
talytech,

See the attached workbook "Insert Rows - VBA_SAMPLE(1) - talytech - SDG09.xls" with macro "InsertRows".

Detach the workbook, and run the "InsertRows" macro.


Have a great day,
Stan

talytech
10-27-2009, 07:50 AM
wow!!! thanks alot.

talytech
10-28-2009, 11:37 AM
Hi stanleydgrom!! I was wondering if you can help me one more time. Now I need a copy row macro. Similar to my previous scenario, I need to populate the empty rows with a copy from the row above if there is 1 blank row. If there are 2 blank rows then the copy would come from 2 rows above. Thanks for your assistance.

By the way, I love vbaexpress forum. I've learned so much.

talytech
10-28-2009, 11:59 AM
Hi stanleydgrom!! I was wondering if you can help me one more time. Now I need a copy row macro. Similar to my previous scenario, I need to populate the empty rows with a copy from the row above if there is 1 blank row. If there are 2 blank rows then the copy would come from 2 rows above. Thanks for your assistance.

PS: I've attached a sample of the spreadsheet.

By the way, I love vbaexpress forum. I've learned so much.

mdmackillop
10-28-2009, 03:12 PM
To copy down to one cell at a time, or a selection

Sub CopyDown()
If ActiveCell.Offset(-1) = "" Then
ActiveCell.Offset.End(xlUp).Copy ActiveCell
Else
ActiveCell.Offset(-1).Resize(2).FillDown
End If
End Sub

Sub CopyDown2()
Dim cel
For Each cel In Selection
If cel.Offset(-1) = "" Then
cel.Offset.End(xlUp).Copy cel
Else
cel.Offset(-1).Resize(2).FillDown
End If
Next
End Sub

talytech
11-04-2009, 10:05 AM
Hey mdmackillop: I can't seem to get your code to work in my loop. Here's what I'm trying to do: I'm looping through the spreadsheet and when I get to a row where there is nothing in cell(a,B) then I want to copy the data from the row above. So in my example below, a message box displays "empty cell". This is where I need to tell it to copy the row above it. The entire row shouldn't be copied just a range (B:F).


Sub CoypDownTest()
Dim lastrow As String
Dim a As Long, CommaCnt As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
For a = 1 To lastrow
If Cells(a, "b") = "" Then
MsgBox "empty cell"
Else
MsgBox Cells(a, "b").Value
End If

Next a
Application.ScreenUpdating = True
End Sub

mdmackillop
11-04-2009, 12:51 PM
Sub CopyDown2()
Dim cel
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(i, 2) = "" Then
Cells(i, 2).Offset(-1).Resize(2, 5).FillDown
End If
Next
End Sub

talytech
11-05-2009, 07:28 AM
Thanks very much. I was struggling and it seems like I was not even close to getting it. Thank you.