Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Add or Manipulate Groups of Multiple Rows
Hi All,
In a scheduling worksheet, first and last names are listed in the left columns, formulas in columns to the right of the names, and then individual days are added daily in columns to the right of the formulas. Marks are made in the intersections of days and people. I would like to have a cmd button or menu to add new people. The tricky part is that 1 person's info occupies 4 consecutive rows. Each row contains different formulas that would have to be included in the copy. It would be best if users could click on any 1 of the 4 rows of an existing person's record, and then click the cmd to add a new person's record (4 new rows) either above or below the existing person's record. Code would have to recognize where one group of rows starts and ends. Note that each person's name is put on the top row (cols A and B) of his or her record, so the presence of text 0 or 1 or 2 or 3 lines above where the cursor is could be the identifying feature that could maybe make this happen. I just don't know how. Any help would be greatly appreciated. Thanks, Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Add or Manipulate Groups of Multiple Rows
Eric,
Try the code below. Assumes that last names are in column B, first names in A. HTH, Bernie MS Excel MVP Sub InsertBlockAboveOrBelow() Dim myCell As Range Dim newFName As String Dim newLName As String newFName = InputBox("What is the new first name?") newLName = InputBox("What is the new last name?") If Cells(ActiveCell.Row, 1).Value = "" Then Set myCell = Cells(ActiveCell.Row, 1).End(xlUp) Else Set myCell = Cells(ActiveCell.Row, 1) End If With myCell.Resize(4).EntireRow If MsgBox("Above = ""Yes"", Below = ""No""", vbYesNo) = vbYes Then .Copy .Insert myCell.Offset(-4).Value = newFName myCell.Offset(-4,1).Value = newLName Else .Copy .Offset(4).Insert myCell.Offset(4).Value = newName myCell.Offset(4,1).Value = newLName End If End With Application.CutCopyMode = False End Sub "Arnold" wrote in message oups.com... Hi All, In a scheduling worksheet, first and last names are listed in the left columns, formulas in columns to the right of the names, and then individual days are added daily in columns to the right of the formulas. Marks are made in the intersections of days and people. I would like to have a cmd button or menu to add new people. The tricky part is that 1 person's info occupies 4 consecutive rows. Each row contains different formulas that would have to be included in the copy. It would be best if users could click on any 1 of the 4 rows of an existing person's record, and then click the cmd to add a new person's record (4 new rows) either above or below the existing person's record. Code would have to recognize where one group of rows starts and ends. Note that each person's name is put on the top row (cols A and B) of his or her record, so the presence of text 0 or 1 or 2 or 3 lines above where the cursor is could be the identifying feature that could maybe make this happen. I just don't know how. Any help would be greatly appreciated. Thanks, Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Add or Manipulate Groups of Multiple Rows
Thanks for responding Bernie. I will have time to try this out later
today and will post back the results. Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Add or Manipulate Groups of Multiple Rows
I tried this code and it will allow a user to insert a row for a new
person below or above the current cell position. However, if the current cell is within another person's record (4 consecutive rows), the new row will split this and be inserted within the record. I would like for the new person's row to be entered either before the current record's top row (contains the the person's first and last names in cols A and B), or below the current record's last row (3 rows down from the row with the first and last names in cols A and B). Also, the code above only copies formulas for the current row and carries them into a new row--but each of the 4 rows of a person's record contain different formulas. I would like for code to carry all of the formulas in the 4 different rows into 4 new rows properly-- formulas should remain but values should revert back to null or default values. I thought about putting a template range of 4 rows containing formatting and formulas on a separate sheet, but this won't work because days will be added in the main sheet, so the template will not match up when inserted into the schedule sheet with code. Thanks again Eric |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Add or Manipulate Groups of Multiple Rows
Arnold,
If you are seeing the behavior that you describe, then the first column of your record isn't truly blank. Try selecting a cell in one of those rows and running this macro: Sub Test() MsgBox IIf(Cells(ActiveCell.Row, 1).Value = "", _ "The cell is blank", "The cell only looks blank") End Sub You will need to delete the spaces or other values in the seemingly blank cells. HTH, Bernie MS Excel MVP "Arnold" wrote in message oups.com... I tried this code and it will allow a user to insert a row for a new person below or above the current cell position. However, if the current cell is within another person's record (4 consecutive rows), the new row will split this and be inserted within the record. I would like for the new person's row to be entered either before the current record's top row (contains the the person's first and last names in cols A and B), or below the current record's last row (3 rows down from the row with the first and last names in cols A and B). Also, the code above only copies formulas for the current row and carries them into a new row--but each of the 4 rows of a person's record contain different formulas. I would like for code to carry all of the formulas in the 4 different rows into 4 new rows properly-- formulas should remain but values should revert back to null or default values. I thought about putting a template range of 4 rows containing formatting and formulas on a separate sheet, but this won't work because days will be added in the main sheet, so the template will not match up when inserted into the schedule sheet with code. Thanks again Eric |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Add or Manipulate Groups of Multiple Rows
Ah, you're right. All 4 rows of of a person's record in my test
spreadsheet had the person's name (as in a non-normal flat structure) for sorting purposes. Thanks Bernie. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose multiple groups of columns to multiple rowsrow | Excel Worksheet Functions | |||
Sorting groups of multiple rows | Excel Discussion (Misc queries) | |||
Creating stacked bar for multiple groups and multiple years | Charts and Charting in Excel | |||
Align/Manipulate multiple chart on one page | Charts and Charting in Excel | |||
Sum rows in groups | Excel Worksheet Functions |