Solved

Excel97 - insert rows

Posted on 2001-06-14
14
491 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 30 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will show you how to use shortcut menus in the Access run-time environment.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

738 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