add rows

[wilg]

Hi, I know there is lots of add rows examples out there but I need a little more help in doing this one..

I want ability to use a macro button to prompt.....

a input box to ask how many rows do you need to add.

Once number of rows keyed eg 2 I need to copy the last 4 rows(each of their needed rows is actually 2) of my worksheet which is referenced in cell AK301 (This is a cell that tells me what the last row on my worksheet is that people use eg row 338)

therefore is they inputed they need 2 rows added I want rows 334:337 selected, copied and inserted to keep formulas in these rows intact.

I'm lost on how to ref the rows based on the input...your help is appreciated very much.

[Kenneth Hobs]

You lost me. Either explain more or post a before and after example.

[wilg]

Hi Kennith, sorry for delayed reply.


In range ak301 is the last row of my sheet (EG "338" which is the row number)


code I'm trying to build....

[vba]Sub Add_Rows()

Dim Inputstrng As String
Inputstrng = InputBox("How many rows do you need to add?", " ASSISTANCE.")
If Inputstrng = "" Then Exit Sub
If Inputstrng = " " Then Exit Sub


Selection.Copy
Rows("ak" & (range("ak301"), -2)' to inputstring value 'how ever many rows needed select upwards x2 (EG; if input string is 4 then select 8 rows

Selection.Insert Shift:=xlDown
End Sub

[/vba]
I'm sorry this is so very ugly, let me know if I need to explain more..

[wilg]

I've been working on solving this and have gotten further but need help on using offset function for

offset -1 for range ak301
and using the inputstrng value x2 to select rows offset (-) by that number up..

[vba]Sub Add_Rows1()

Dim Inputstrng As String
Inputstrng = InputBox("How many rows do you need to add?", " ASSISTANCE.")
If Inputstrng = "" Then Exit Sub
If Inputstrng = " " Then Exit Sub

Rows(Range("Ak301") & ":" & Range("Ak302")).Select ' I really want range ("A1" minus one) : inputstrng value (times 2)
Selection.Copy
Rows(Range("Ak301") & ":" & Range("Ak302")).Select
Selection.Insert Shift:=xlDown
ActiveCell.Select
End Sub
[/vba]

[Aussiebear]

Not sure what you mean here, but as I read it you are seeking "A1 minus 1" which is not possible as A1 is the cell in the uppermost & leftmost cell.

[wilg]

Sorry my error[/QUOTE]My fault. Was meaning actually ak301 value minus one.

Range ak301 holds the row number of the last row i use.

I want to select the two rows above that row number or from the inputbox the value of the inputbox times two select that many rows above the row number that is stored in range ak301

[wilg]

I have completed the ability to copy the 2 rows I want based on the value in ak301 below.

As for how many rows the user wants to add, I need to use the inputstrng value to repeat the selection.copy and insert that many times.......

any further help is appreciated..

Mike.


[VBA]Sub Add_Rows4()
'TEST TO USE INPUTBOX and copy same selection multiple times
Application.ScreenUpdating = False
Application.CutCopyMode = False
Dim Inputstrng As String
Inputstrng = InputBox("How many rows do you need to add?", " ASSISTANCE.")
If Inputstrng = "" Then Exit Sub
If Inputstrng = " " Then Exit Sub

Rows((Range("Ak301").Value - 1) & ":" & (Range("Ak301").Value - 2)).Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveCell.Select
Application.ScreenUpdating = True
[/VBA]

[shrivallabha]

Try the following routine. It will insert specified number of rows at the activecell:
[VBA]Public Sub AddCustomRows()
Dim iCount As Integer
iCount = Application.InputBox("Please enter no. of rows!", "InsertRows", Type:=1)
For i = 1 To iCount
ActiveCell.EntireRow.Insert Shift:=xlDown
Next i
End Sub
[/VBA]

[wilg]

Thanks for all your help, with your examples i've been able to come up with this that works perfectly....

except when the user select column a I have a userform that opens, with the code below as is it activates the userform when adding the row...

Is there a way to not activate column A when running the code below?

[VBA]Sub Add_Rows()
Dim i As Integer
Application.ScreenUpdating = False
Application.CutCopyMode = False
Dim Inputstrng As String
Inputstrng = InputBox("How many rows do you need to add?", " ASSISTANCE.")

If Not IsNumeric(Inputstrng) Then Exit Sub

If CInt(Inputstrng) < 1 Then Exit Sub

For i = 1 To CInt(Inputstrng)
Rows((Range("Ak301").Value - 1) & ":" & (Range("Ak301").Value - 2)).EntireRow.Select
Selection.Copy

Selection.Insert Shift:=xlDown


Next i
Application.CutCopyMode = False
ActiveCell.Select
Application.ScreenUpdating = True
End Sub
[/VBA]

[mdmackillop]

[VBA]
Sub AddRows()
Dim LastRow As Long
Dim Rws As Long
Rws = InputBox("How many rows do you need to add?", " ASSISTANCE.")
LastRow = Cells(Rows.Count, 1).End(xlUp).Row '<===Modify to suit
Cells(LastRow, 1).Offset(-(2 * Rws) + 1).EntireRow.Resize(Rws * 2).Copy Cells(LastRow + 1, 1)

End Sub

[/VBA]