Writing Macros in Microsoft Excel 2010

[Fonzie1104]

Hey guys,

So I am pretty new to the Macro world but the thing I want to be able to do is to insert lines based on specific criteria. I have read a few other posts but my requirments are a little more in depth. I'll try and explain the best I can but the information that I have is presented in a list. The user would go to that specific cell containing the list, and when a string is chosen within that list, rows would be inserted below depending on what is chosen. For example, say that cell C5 contains a list with data including "Fruit", "Vegetables", and "Empty". If the user were to select "Fruit", 3 rows would be inserted below (in C6-C8) with strings stating "Apples" in C6, "Oranges" in C7, and "Pears" in C8. If that user were to go back to the list (in C5) and select "Vegetables", the rows that were created for fruit (in C6-C8) would be removed and 4 rows would now be insereted below C5 (C6-C9) with strings stating "Carrots" in C6, "Broccoli" in C7, "Beans" in C8, and "Peas" in C9. Lastly, if the user were to choose "Empty", the rows that were created for "Vegetables" would be removed and there would be nothing below C5. (Note: the list that would be created in C5 would also be created in C6. Therefore when rows are added based on the string chosen, they would be inserted in between the two lists found in C5 and C6) I appologize if this is confusing but any help would be much appreciated. Also please let me know if any further information would be helpful.

 

[Stoneboysteve]

Fonzie,

You can use the worksheet change event to drive the inserts, and within the change event use a case statement to pick the number of rows.

The case statement psudocode:

selection = activecell.value
case selection
fruit
code for fruit
vegetables
code for vegetables
else
code for else
end case.

Your requirements for deleting rows based on additional criteria seem nearly impossible to manage if the user inserts a row, the code will have to be selective about what is removed. Tricky at best, sounds like a test question

Here are a few code snips that may help you:
adding a single row:
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Adding two rows:
Rows("4:6").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Deletes row 10:
Rows("10:10").Select
Selection.Delete Shift:=xlUp

Deletes rows 12 and 13:
Rows("12:13").Select
Selection.Delete Shift:=xlUp

Other tips:
Offset- lets you 'drive' from the active cell. Handy if you are selecting from a cell and need to change focus to the cell below.

Change event:
Private Sub Worksheet_Change(ByVal Target As Range)
'''code goes here!
End Sub

Good luck,

Stoneboysteve