?
Solved

Excel97 - insert rows

Posted on 2001-06-14
14
Medium Priority
?
496 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:capital052798
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 13

Expert Comment

by:cri
ID: 6190591
0
 
LVL 1

Expert Comment

by:Uziel
ID: 6190593
"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
 

Author Comment

by:capital052798
ID: 6191775
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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:capital052798
ID: 6191777
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
 
LVL 1

Expert Comment

by:Uziel
ID: 6191923
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
 
LVL 1

Expert Comment

by:Uziel
ID: 6192010
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
 

Author Comment

by:capital052798
ID: 6201509
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
 
LVL 13

Expert Comment

by:cri
ID: 6204018
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
 

Expert Comment

by:amp072397
ID: 6209954
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
 

Author Comment

by:capital052798
ID: 6213661
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
 
LVL 13

Accepted Solution

by:
cri earned 120 total points
ID: 6216333
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
 

Expert Comment

by:amp072397
ID: 6220398
Rejected proposed answer because of capital's statement:

>I thought I had unlocked this!

amp
0
 

Author Comment

by:capital052798
ID: 6224128
cri
your solution wasn't quite what I had in mind BUT it seems to work OK.
Thanks
0
 
LVL 13

Expert Comment

by:cri
ID: 6225385
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

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
New style of hardware planning for Microsoft Exchange server.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question