At first, please look this excel worksheet: enter image description here

The month table contains whole year data. If I select any month from the table by typing or by selecting from drop down menu (e.g "Selected") then only those rows will be appeared after certain rows. As for example I want that the selected rows will be appeared after rows 14 and onward (i.e Rows will not be appeared before row 15). How to achieve this?

For your kind information, Number of selected rows may vary. As for example, in the above table there are two rows selected (April & September). But it might be March, April & September selected. So I can't specify a cell range in which the selected rows will be appeared. Also, there will be some text on Rows 17. So, the selected rows have to inserted dynamically after rows 14.

Any idea how to achieve this?

  • It could technically be done with a macro as well. Do you want the "appeared" rows to be in order of month, or in order of selection? – Christofer Weber Nov 27 '16 at 12:37
  • In order of selection – Abdullah Mamun-Ur- Rashid Nov 28 '16 at 7:48

I would probably go using a PivotTable. Month would go in COLUMNS while B, C & D in VALUES (this should essentially make the table look more or less the same as yours). The selection parametre then needs to be put into FILTERS to show only the stuff you want.

The bad thing about is that each team you want changes to be displayed, you need refresh the PivotTable (RMB and Refresh or through Analyse; alternatively you can use a macro to do it periodically for you).


Step 1 - The list

First you can add the "Selected" part with a drop down list.
With E1 selected, go to the Data tab and select Data Validation. Select List and write Selected as Source.
Now drag E1 down to E12 to get the list on all the cells.

Step 2 - The Code

Adding rows
To keep track of these cells changing a Worksheet_Change Sub works great. Right click your "Sheet1" (or equivalent) and select Show code.

To track and copy the selected rows, we can do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer, r As Integer
On Error GoTo EndM
If Target.Value = "Selected" And Target.Column = 5 And Target.Row < 13 Then
    For r = 15 To 30
        If Cells(r, 1).Value = "" Then
            Range("A" & r & ":D" & r).Cells.Value = Range("A" & Target.Row & ":D" & Target.Row).Cells.Value
            i = 1
        End If
        If i > 0 Then GoTo EndM
    Next r
End If
End Sub

This looks for changes in the sheet. And if the change any cell from E1 to E12 being changed to "select", then it copies the columns A to D of the same row, into the first empty row starting from row 15.
This does not stop you from selecting the same entry twice, i.e. getting two rows of January. It's also set to a limit of row 30. After row 30 it will not add more entries Can be changed by changing the value 30 in For r = 15 To 30

Deleting rows
We can use basically the same method to delete the rows again when we delete the "Selected" value by adding

If Target.Column = 5 And Target.Row < 13 And Target.Value = "" Then
    For r = 15 To 30
        If Cells(r, 1).Value = Cells(Target.Row, 1) Then
            Range("A" & r & ":D" & r).Cells.Delete
        End If
    Next r
End If

Between End If and EndM: in previous example.

The Error handler is needed for the sheet not to throw an error at you every time you try to delete multiple cells at a time.
This will not run if you delete multiple cells at one time, and will not delete every row if the same month is present twice in a row.

You can of course add or change functionality, like restricting the addition of multiple entries by adding:

            ElseIf Cells(r, 1).Value = Cells(.Row, 1).Value Then
            MsgBox ("Multiple entries are not allowed!")
            GoTo EndM

Between i = 1 and End If in the Adding part.
Good luck!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.