Excel97 - insert rows

I would just like a simple VBA to insert a row in between current rows that contain data.

Basically if row contains data insert row
go to next row test again loop until blank row
capital052798Asked:
Who is Participating?
 
criConnect With a Mentor Commented:
Put this in the code window at the _workSHEET_ level of the required sheet:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  'Monitors column A for manual entries
  'Loop through entire column A and inserts an empty row if required
  'Room for improvement:
  ' - Does not delete row if cell Ax cleared
  ' - If too slow and sheet ok, modify to check change only =>faster
 
  Dim i As Integer
   
  With Target
    If .Column = 1 Then '<< Adapt if necessary
      With .Parent.UsedRange
        For i = 1 To .Rows.Count
          If IsEmpty(.Cells(i, 1)) = False And IsEmpty(.Cells(i + 1, 1)) = False Then
            .Rows(i + 1).EntireRow.Insert
          End If
        Next i
      End With
    End If
  End With
End Sub
0
 
UzielCommented:
"Selection.EntireRow.Insert" comes to mind.

You could also replace Selection with a range.

"Range("B11:B12").EntireRow.Insert"

Leave out the Quotation marks.  :)

Hope that helps

Uziel
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
capital052798Author Commented:
Er - thanks BUT I can't write any VBA myself.
I'm not too bad at amending code.
Could have the actual code - line by line
from "Sub" to "End Sub" please.
Thanks
0
 
capital052798Author Commented:
Er - thanks BUT I can't write any VBA myself.
I'm not too bad at amending code.
Could have the actual code - line by line
from "Sub" to "End Sub" please.
Thanks
0
 
UzielCommented:
This works, but you have to define the range.  I have yet to come up with a way for a For each to know how to skip rows.  I'm sure Cri or Cal can do it, but I'm just a newb compared to them...  ;)

Hope that helps.

Sub InsertRows()
Dim Cel, Rng

Set Rng = Range("A1:A6")
Rng.Select
i = Rng.Count - 1
For i = i To 0 Step -1
ActiveCell.Offset(1).EntireRow.Insert
ActiveCell.Offset(2).Select
Next i

End Sub
0
 
UzielCommented:
This works, but you have to define the range.  I have yet to come up with a way for a For each to know how to skip rows.  I'm sure Cri or Cal can do it, but I'm just a newb compared to them...  ;)

Hope that helps.

Sub InsertRows()
Dim Cel, Rng

Set Rng = Range("A1:A6")
Rng.Select
i = Rng.Count - 1
For i = i To 0 Step -1
ActiveCell.Offset(1).EntireRow.Insert
ActiveCell.Offset(2).Select
Next i

End Sub
0
 
capital052798Author Commented:
Uziel - thanks but I don't really want to specify a range.
I've come up with the following code that works OK but only tests if the active cell has data in it or not (rather than the entire row).

I really need something like
Do While ActiveRow.Offset(1) > 0 - but this isn't valid code!

Sub InsertRow()
Do While ActiveCell.Offset(1) > 0
   ActiveCell.Offset(1).EntireRow.Insert
    ActiveCell.Offset(2).Select
Loop
End Sub
0
 
criCommented:
capital, please state:

a) Do you have a column which is filled for sure in the unprocessed state ?

b) Does this have to be updated automatically or do you need/prefer to have this manually ? If automatical: How is the sheet filled ? By hand or by some remote process ?

c) Is this
 - For a particular worksheet
 - For all workhsheets of a particular workbook
 - All workbooks (manual update only)
0
 
amp072397Commented:
Hi, Uziel:

You have locked capital's question so that it no longer appears in the open question list. In most topic areas here at EE, Experts don't post as answer unless they are absolutely sure that their comment is the best and only solution, and that it does not repeat a prior comment (which is very rare). Your comment is quite similar to the one referenced by cri.

By locking the question, capital cannot get the benefit of input by everyone. Please see this link: http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp

capital:

Feel free to reject Uziel's proposed answer. I'm sure he/she won't be offended.

Thanks everyone!
amp
Community Support Moderator
Experts Exchange
0
 
capital052798Author Commented:
I thought I had unlocked this! Anyway per my previous comment
"Uziel - thanks but I don't really want to specify a range.
I've come up with the following code that works OK but only checks to see if the active cell (within a specific column) has data in it or not (rather than the entire row).

I really need something like
Do While ActiveRow.Offset(1) > 0 - but this isn't valid code!

Sub InsertRow()
Do While ActiveCell.Offset(1) > 0
  ActiveCell.Offset(1).EntireRow.Insert
   ActiveCell.Offset(2).Select
Loop
End Sub "

Re Cri's questions

a) Do you have a column which is filled for sure in the unprocessed state ? YES
b) Does this have to be updated automatically or do you need/prefer to have this manually ? If automatical:
How is the sheet filled ? By hand or by some remote process ? MANUAL

c) Is this
- For a particular worksheet - YES

0
 
amp072397Commented:
Rejected proposed answer because of capital's statement:

>I thought I had unlocked this!

amp
0
 
capital052798Author Commented:
cri
your solution wasn't quite what I had in mind BUT it seems to work OK.
Thanks
0
 
criCommented:
capital, thAnk you. I can modify it if you want, depending on your needs/preferences:
- Manual triggering ?
- Speeding up (assuming the sheet is formatted already

Question: Why do you not simply increase the row height if you want to separate the entries a bit ? Or alternate the background color (i.e. make stripes), can be automated.

amp, thank you for your attentiveness.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.