Solved

Excel97 - insert rows

Posted on 2001-06-14
14
474 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
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 13

Expert Comment

by:cri
Comment Utility
0
 
LVL 1

Expert Comment

by:Uziel
Comment Utility
"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
Comment Utility
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
 

Author Comment

by:capital052798
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 13

Expert Comment

by:cri
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Rejected proposed answer because of capital's statement:

>I thought I had unlocked this!

amp
0
 

Author Comment

by:capital052798
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Outlook Free & Paid Tools
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now