PDA

View Full Version : Insert row code help



alok2007
06-01-2007, 12:05 AM
I have found a code which inserts row & fill formula The code related to protect & unprotect sheet has been put by me. Now I want some modiafication in the code if some one can provide the same it will be useful for me. I self does not have any depth idea on macros so please help-

Sub InsertRowsAndFillFormulas()
ActiveSheet.Unprotect Password:="abcd"
Dim vRows As Long
Dim sht As Worksheet, shts() As String, i As Long
' row selection based on active cell --
ActiveCell.EntireRow.Select
vRows = _
Application.InputBox(prompt:= _
"How many rows do you want to add? This will insert " & _
"a new blank row below current selected row", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number
If vRows = False Then Exit Sub
'if you just want to add cells and not entire rows
' then delete ".EntireRow" in the following line
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
'insert rows on grouped worksheets
' rev. 2001-01-17 Gary Brown
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name
Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown
Selection.AutoFill Selection.Resize(rowsize:=vRows + 1), _
xlFillDefault
On Error Resume Next
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
'reselect original group - Dave McRitchie 01/17/2001
Worksheets(shts).Select
ActiveSheet.Protect Password:="abcd"
End Sub

1. On running the code ,if the user clicks cancel, the sheet becomes unprotect & I want that even if the user clicks cancel the sheet will not be unprotected i.e will remain protected.
2. It should insert a row at the end of the last row & immediately above the row containg the term "Total" (ie if in a sheet any cell at any row contains the term "total", the code will insert a row above that "total" row else it will insert a row at the end of the last row)
3. It only copies down the formula from the previous row & not values. I want that if cells in the above row contains formula it will copy the formula (as the code is doing now) but if the above row contains values it will copy the value. Please help.

alok2007
06-01-2007, 05:55 AM
Any help!!!!!!!!!!

alok2007
06-01-2007, 09:33 PM
No help!!!1 Someone plz give a look!!!!!!!!

bigdoggit
06-01-2007, 11:45 PM
In response to your questions, I wrote out some explanations.




1) On running the code ,if the user clicks cancel, the sheet becomes unprotect & I want that even if the user clicks cancel the sheet will not be unprotected i.e will remain protected.



For this one, you have two options that come to mind right away.
One would be to not unprotect the worksheet until the user chooses how many rows to add. If they choose cancel, you could exit sub, and since no worksheets have been unprotected, they will remain protected.
Two would be instead of just exiting sub, jump to the end of your code which includes a line protecting your active worksheet.
I tend to use method two, so if I change things later, I am less likely to create a problem by deleting an important line. If every time I exit the sub, I send the program to my "Goodbye" line of code, I know certain actions will always be preformed. So I put actions there that I will always want done. I did solution two to your code.


2) It should insert a row at the end of the last row & immediately above the row containg the term "Total" (ie if in a sheet any cell at any row contains the term "total", the code will insert a row above that "total" row else it will insert a row at the end of the last row)



For questions 2, I am unsure of what you are asking. You want to add a row at the end of your last row. To me, this means below your last row. But, below your last row are just empty rows, so why add one? As for adding a row above any row which has a cell containing "Total", that has been done by searching for "Total", then looking for more instances of that word, adding a row above each time "Total" is found. I did not try to match case, so if "Total" is found, or if "ToTaL" is found, excel will still add a row. As it currently stands, if you have "Total" more than once in a single row, a row for each occurrance will be added directly above. Now, I have just coded to to add a row. This will be a blank row. If you would like your selection copied below your last row and above rows with "Total" in them, then I will have to tweak the code somewhat.


3) It only copies down the formula from the previous row & not values. I want that if cells in the above row contains formula it will copy the formula (as the code is doing now) but if the above row contains values it will copy the value. Please help.


For this one, everything is working exactly how you are telling the computer to make it work. In this case, your are telling it too much perhaps. I noticed you had the line:
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents

This line clears all constants. So you are copying both formula and constants, then clearing all the constants. Everything happens so fast, it appears as though only formula are being copied, not constants. You mentioned 'values', and by this I interperated you meant numbers. xlConstants includes numbers, so these would be cleared as well.

Now I did change xlFillDefault to xlFillCopy because I interperated that you wanted values copied, not autofilled. If you would want your numbers autofilled, then change xlFillCopy back to xlFillDefault.


Hopefully I have helped some. Please let me know if there is more that I can do. I will try and pay attention to this thread, but I have company this weekend, and I'm not sure what time I will have. Still, I will check in soon.

Big E


P.S. - The modified code is below. Just copy it and paste it into a module to test and verify this is what you want. I did add asteriks (*) above and below where I made changes as well as noted that I made changes. I hope this helps you to see what I tweaked quicker.





Sub InsertRowsAndFillFormulas()
ActiveSheet.Unprotect Password:="abcd"
Dim vRows As Long
Dim sht As Worksheet, shts() As String, i As Long

'********ADDED LINEs BELOW*********
Dim rTotal As Range
Dim rLastLine As Range
Dim rTmpRng As Range
'********ADDED LINEs ABOVE*********


' row selection based on active cell --
ActiveCell.EntireRow.Select
vRows = _
Application.InputBox(prompt:= _
"How many rows do you want to add? This will insert " & _
"a new blank row below current selected row", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number

'*********CHANGED THE LINE BELOW***********
If vRows = False Then GoTo GoodBye
'*********CHANGED THE LINE ABOVE***********

'if you just want to add cells and not entire rows
' then delete ".EntireRow" in the following line
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
'insert rows on grouped worksheets
' rev. 2001-01-17 Gary Brown
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown



'******CHANGED THE LINE BELOW***********
Selection.AutoFill Selection.Resize(rowsize:=vRows + 1), _
xlFillCopy
'******CHANGED THE LINE ABOVE***********


On Error Resume Next
' to remove the non-formulas -- 1998/03/11 Bill Manville

Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents



'*********ADDED LINES BELOW**********
Set rLastLine = sht.Cells.Find(what:="*", After:=sht.Cells(sht.Rows.Count, sht.Columns.Count), _
LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)

Set rTotal = sht.Cells.Find(what:="Total", After:=sht.Range("A1"), _
LookIn:=xlValues, Lookat:=xlWhole)
If Not rTotal Is Nothing Then
rTotal.EntireRow.Insert Shift:=xlShiftDown
Set rTmpRng = rTotal
Do
Set rTotal = sht.Cells.FindNext(rTotal)
rTotal.EntireRow.Insert Shift:=xlShiftDown
Loop Until rTmpRng <> rTotal
End If
'*********ADDED LINES ABOVE**********






Next sht
'reselect original group - Dave McRitchie 01/17/2001
Worksheets(shts).Select

GoodBye:
ActiveSheet.Protect Password:="abcd"
End Sub

alok2007
06-02-2007, 12:26 AM
Copied and pasted your code & when run, the screen starts vibrating & around after 3-4 minutes I have to use Ctrl+Alt+Del to close Excel & thus could test your code.Any modification?

bigdoggit
06-02-2007, 12:40 AM
I tested mine on a workbook that contained 3 sheets. How many does yours contain? Also, can you post your workbook so I can test the code in your environment? Lastly, I am running Excel 2003 - what version is yours?

alok2007
06-02-2007, 01:09 AM
I am also using 3 sheets & Excel 2003 Win XP.A sample wb is attached.

bigdoggit
06-02-2007, 01:50 AM
okay - I'm playing with it right now. I realized my error. The line:

Loop Until rTmpRng <> rTotal

needs to be:

Loop Until rTmpRng = rTotal

I am still playing with some other bugs, but check this out and tell me what isn't working right yet.

alok2007
06-02-2007, 03:11 AM
I'll test the code and come out with the bugs if found

alok2007
06-18-2007, 12:15 AM
Sorry, I came late as I was on leave for these days. Anyway, I have tested the code and the observations are:

1. If you place the cursor at the last record then it inserts three rows instead of one row.
Suppose if you keep the cursor at B12 and run the code, it will insert three rows. At row 13, it fills the formula whereas rows 13 & 14 are blank. Whereas I want that only one row at row13 shall be inserted.

2. If you place the cursor between rows it inserts one row below that row and two rows above the total row.
Suppose if you keep the cursor at B10 and run the code, it will insert a row at 11 and two rows at 14 & 15.Whereas I want that one row should be entered at 13 only and push down the row containing word ?Total? .

3. In case you keep the cursor at the row containing the word ?Total? it inserts two rows above the row and also inserts a row below the total row.
Suppose you keep the cursor at row 13, you will find that two rows at 13 & 14 has been inserted and also one row at row 16 inserted whereas I want that only one row at row 13 shall be inserted and push down the row containing the word ?Total?.

4. In case there is no row containing the word ?Total?, it inserts one row below the current selected row and not after the last record.
In case you go to? Employeedata? sheet where there is no row containing the word ?Total? and if you run the code keeping the cursor at A10, it will insert a row at row 11 where as I want that it shall insert a row after the last recode ie at row 13.


I think this will be useful but in case more clarification is needed, do let me know.

alok2007
06-18-2007, 09:40 PM
Bump!!!!

alok2007
06-18-2007, 09:41 PM
Bump!!!!!

alok2007
06-20-2007, 09:53 PM
Any help please!!!!!!!!!!!!

Bob Phillips
06-21-2007, 02:14 AM
The code seems incredibly complex for a s imple task.

So all you want is tgo insert a set number of rows in the Other Employee benefits sheet, is that correct.

Are there restrictions on where you can insert, for instance, does it have to be between the heading and total rows, otherwise exit garcefully?

BTW, are you really in Bhutan?

alok2007
06-21-2007, 02:59 AM
The code that I have provided earlier in the 1st post inserts a row and also fills formula copied from the above row below which the row is inserted.

However my problem is that you can insert a row(s) anywhere in the sheet as it inserts row just below the selected cell , and this I want to restrict. It shall insert a row only after the last record irrespective of the position of the cursor in the sheet.
To clarify, "employeedata" the code should insert a row at row 13 since row 12 is the last record and row 13 shall copy formula from row 12.

But if in the sheet you have a row containing the word "Total" in that case it shall insert a row above that row and copy & fill the formula from its above row.
To clarify, "other employee benefit" sheet contains a row with "Total" at row 13 so the code should insert a row at row 13, pull down existing row 13 to row 14 and row 13 shall copy formula from row 12.

i think this will clear your doubt and will help you to design the code. The input box asking for "How many rows you want to insert" may be deleted and one row maty be inserted on one run of the code.

In case you need more clarification, please get back to me.

Regarding my location, I am from north eastern region of India but is now working at Bhutan and will shift back to India shortly.