Insert rows based on value per day

M

Mayte

would anybody know how to inseart x numbers of rows based on the value
entered? I need to enter different amount of rows every day based on the # of
contacts we've had and the # do vary per day. we have a template set up per
day and we have to be adding ot deleting rows because we have 50 each day set
...so anyway to make it automatic?

Column A: # of rows needed
Column B: Date
Column C: Customer Name
Column D: Comments

Let's say I need 2 rows for 04/29/08, 5 rows for 04/30/08 and 10 rows for
05/01/08. is there any way that I would get blank rows auto added ased on the
numbers or rows enter in column A per day?
 
M

Mayte

Never mind ...sorry ! I found the answer searching for similar posts ...
should've searched more b4 posting ... sorry & thanks !!

Sub splenda()
'Capture row of last entry in column B
ctrlValueRow = Cells(Rows.Count, "A").End(xlUp).Row
'Loop upward thru cells in ColB; insert as needed
Do While ctrlValueRow > 0
Cells(ctrlValueRow, 2).Activate
If ActiveCell.Value > 0 Then
For i = 1 To ActiveCell.Value
ActiveCell.Offset(1, 0).EntireRow.Insert
Next i
End If
ctrlValueRow = ctrlValueRow - 1
Loop
End Sub

Cheers,
Mayte
 
R

ryguy7272

This macro will insert a variable number of rows:
Sub InsertAnyRows()

Dim insertNumber As Range
Dim insertStart As Range
Dim redRng As Range
Dim i As Integer

Set insertNumber = Application.InputBox _
(Prompt:="Select a point to begin inserting rows. For instance, choose first
non blank cell in Column A", Title:="Add a row", Type:=8)
insertNumber.Select
If insertNumber <= 0 Then
MsgBox ("Invalid Number Entered")
Exit Sub
End If
Dim myRow As Long

lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = 1
Do Until myRow = lastcell
For i = 1 To Cells(myRow, 1)

If Cells(myRow, 1) <> "" Then
Cells(myRow + 1, 1).Select
Selection.EntireRow.Insert shift:=xlDown
End If

Next
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = myRow + 1
Loop


End Sub

Regards,
Ryan---

PS, I didn't create this. Some brilliant person shared it with me a little
over a year ago, and now a not-so-brilliant person is sharing it with you now.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top