• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Insert row only if Column C = 0

C

Cammandk

Member
I am looking to allow a user to insert a row using a macro and to copy a preformatted line into that position if criteria met. The conditions to be met are:

1. The cursor can be in any column on the sheet (Sheetname CostManager).

2. The line will be inserted at the row of the cursor.

3. If the value in Column C on the row is "1" then the line cannot be inserted at that row and Msg Box should pop up to say "ABCD"

4. If the value in Column C on the row is "0" then the row is inserted and a named range "LineInsert" is copied to the new row.

Is it possible to select how many lines are inserted and copied - say 1 to 5?

Hope someone can help with this as central to my user form working.

Thanks
 
Here's an example of checking rows, and choosing how many rows to insert:
Code: 
Sub test()
Dim myInput As Integer
Dim myRow As Long
Application.ScreenUpdating = False
myRow = ActiveCell.Row
If Cells(myRow, "C").Value = 1 Then
  MsgBox "Can't insert a row here", vbOKOnly, "Invalid row"
  Exit Sub
Else
  myInput = InputBox("How many rows do you want to insert?", "# of Rows", 1)
  Range("LineInsert").Copy
  ActiveCell.Resize(myInput, 1).EntireRow.Insert
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
  
End Sub
 
Hi Luke
Thanks for this. Just tried as - get runtime error 1004 - Method 'range' of object' - global' failed.
Any ideas
 
Hi Luke
Partly resolved - realised that I did not have matching range name to that used in VBA. So inserts line now.

However if you request more than 1 line to insert it only appears to continue to enter only 1 line.

Best regards
David
 
Hi Luke
I've tried to amend your code to allow me to insert a named range that consists of more than 1 row. It fell over at this line. I only want this entered once so deleted the msgbox and option to decide no. Is it because the named range is more than 1 row?
If I can get both of these inserts working then that would be fantastic.
David

ActiveCell.Resize(myInput, 1).EntireRow.Insert
 
If you just want to insert it one time, you can get rid of the Resize method, and just do:
ActiveCell.EntireRow.Insert
 
Hi Luke
Ideally I would like user to be able to insert more than 1 line - say 1-5. So if this is possible great - the previous code only seems to insert 1 even though it asks you for No?
DK.
 
You're right, having an inserted range that is of multiple rows was messing things up. We can multiply the number of rows we need to figure things out.
So, change the one section to:
Code: 
Range("LineInsert").Copy
myInput = myInput * Range("LineInsert").Rows.Count
ActiveCell.Resize(myInput, 1).EntireRow.Insert
 
Have amended this code but it still only inserts one line. Doesn't it need some form of loop / counter?

Thanks
DK
 
After doing some tests, my guess is that the range you have called "LineInsert" is not a whole row of cells, but a partial row. Need to change the name definition to be the whole row, or change the code line to be:
Code: 
Range("LineInsert").EntireRow.Copy
 
You were correct my range was a partial row. Sorry I should have been more precise.
I've changed the code to and it does now insert a multiple of rows but they all become hidden? If I unhide the rows the inserts become visible?
DK
 
Why are they getting hidden? Is there some other part of your code that hides things? Do we want the inserts to be visible/hidden?
 
You must log in or register to reply here.
Back
Top