First reaction, start from AC7, instead of AC1
Compare Excel | Excel Templates | DownloaderXL Pro
Finance Templates & Add-In Bundle | NeuroXL Predictor | Construction Estimating
Merge Excel The Easy Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages
Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build Automated Trading Models In Excel
***Can any one tell me what is wrong with this code please?
I am trying to insert rows from an InputBox in several sections of a sheet then copy the formulas down into the inserted cells in each section.
each row in each section = section one rows
VB:Public rng As Long Sub changesheetsize() Dim AC_7 As Range Dim AC_6 As Range Dim AC_5 As Range Dim AC_4 As Range Dim AC_3 As Range Dim AC_2 As Range Dim AC_1 As Range Set AC_7 = Range("A65536").End(xlUp) Set AC_6 = AC_7.End(xlUp) Set AC_5 = AC_6.End(xlUp) Set AC_4 = AC_5.End(xlUp) Set AC_3 = AC_4.End(xlUp) Set AC_2 = AC_3.End(xlUp) Set AC_1 = AC_2.End(xlUp) Application.ScreenUpdating = False rng = InputBox("Enter number of rows required.") AC_1.Select Set AC_1 = AC_1.Offset(-rng, 0) Call InsertRow AC_2.Select Set AC_2 = AC_2.Offset(-rng, 0) Call InsertRow AC_3.Select Set AC_3 = AC_3.Offset(-rng, 0) Call InsertRow AC_4.Select Set AC_4 = AC_4.Offset(-rng, 0) Call InsertRow AC_5.Select Set AC_5 = AC_5.Offset(-rng, 0) Call InsertRow AC_6.Select Set AC_6 = AC_6.Offset(-rng, 0) Call InsertRow AC_7.Select Set AC_7 = AC_7.Offset(-rng, 0) Call InsertRow AC_7.Select Call fdownformula AC_6.Select Call fdownformula AC_5.Select Call fdownformula AC_4.Select Call fdownformula AC_3.Select Call fdownformula AC_2.Select Call fdownformula AC_1.Select Call fdownformula Application.ScreenUpdating = True End Sub Sub InsertRow() If rng = 0 Then Exit Sub Range(ActiveCell, ActiveCell.Offset(Val(rng) - 1, 0)).EntireRow.Insert End Sub Sub fdownformula() Dim n As Long, k As Long 'need To know how many formulas To copy down. 'Assumes from A over To last entry In row. k = ActiveCell.Offset(-1, 0).Row n = Cells(k, 256).End(xlToLeft).Column Range(Cells(k, 1), Cells(k + Val(rng), n)).FillDown End Sub
First reaction, start from AC7, instead of AC1
Cheers,
S M C
Click To Read: How To Use Tags In Your Threads/Posts
Please take time to read Forum Rules before posting
Message To Cross Posters
Now getting run error 1004
aplication/object defined error
At
VB:Set AC_1 = AC_1.Offset(-rng, 0)
Last edited by LucyLoo; March 2nd, 2012 at 23:33. Reason: remove picture address
Try this..
VB:Sub changesheetsize() Dim rng As Range Dim lngRows As Long lngRows = InputBox("Enter number of rows required.") For Each rng In ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeConstants, 1) rng.Resize(lngRows).EntireRow.Insert rng.Offset(-lngRows - 1).Resize(lngRows + 1, Cells(rng.Row, Columns.Count).End(xlToLeft).Column).FillDown Next rng End Sub
Cheers,
S M C
Click To Read: How To Use Tags In Your Threads/Posts
Please take time to read Forum Rules before posting
Message To Cross Posters
VB:Sub snb() y = InputBox("Enter number of rows required.") For j = 47 To 5 Step -7 Rows(j).Resize(y).Insert Rows(j).Copy Rows(j).Resize(y) Next End Sub
Thanks smc that works great. sorry for the delay in coming back; been out of th office.
Ok I have gone away and tried to learn the special cell method is the below explanation of your code correct. If not would you be so kind to put me in the correct direction please.
would this code run smoother if the rng column A references were at the top left of the inserted cells i.e. the row to be filled down from?VB:Sub changesheetsize() Dim rng As Range Dim lngRows As Long lngRows = InputBox("Enter number of rows required.") Set range colum A, rng=Cells that contain Numbers For Each rng In ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeConstants, 1) Resize? by user Input ( say user Input 10) insert 10 rows rng.Resize(lngRows).EntireRow.Insert offset up 9 rows, Resize? (10+1=11rows, need help? rng.Offset(-lngRows - 1).Resize(lngRows + 1, Cells(rng.Row, Columns.Count).End(xlToLeft).Column).FillDown Next rng
Last edited by LucyLoo; March 8th, 2012 at 00:33. Reason: denote instruction in code
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks