Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert rows based on value per day
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert rows based on value per day
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 "Mayte" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert rows based on value per day
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. -- RyGuy "Mayte" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
How to insert rows based on change in data | Excel Worksheet Functions | |||
Insert new rows based on Data in other rows | Excel Worksheet Functions | |||
Insert Rows Based on values in another worksheet | Excel Discussion (Misc queries) | |||
Insert New Rows based on COUNTA() | New Users to Excel |