How do i order selections contigously

[Zebediah]

On one sheet I have a long list of items which I need my users to scroll down
and, when needed, fill in a value next to the appropriate item in the list.

Most of the list items will not need anything inputting against them.

Then on a different sheet I need a nice tidy summary list which only shows
those list items which have an input against them.

The bit I can’t do is getting these few items to display (on a separate
sheet) in a nice neat all together, contiguous way - ie with no gaps

any ideas?

 

[Don Guillett]

use data>filter>autofilter>copy the filtered

 

[Zebediah]

is there anyway the summary list can be populated automatically? i have a
lot of these forms. Also the users need the summary list themselves, without
intervention from me...if possible.

Many thanks for the quick response though.

 

[Otto Moehrbach]

The question now is; What do you mean by "automatically? Specifically, what
do you want to use as the trigger that induces Excel to do this for you?
For instance, Excel can do this each and every time that an entry is made in
that column that the users use for input. Or Excel can do this whenever the
file is saved. Or closed.. Or opened. HTH Otto

 

[Zebediah]

Otto - By Automatically i would like the trigger to be as an entry is made.
Sorry i didn't clarify this before, and my apologies for not spotting your
reply.

Best regards

 

[Don Guillett]

If desired, send your workbook to my address below along with a complete
explanation and before/after examples.

 

[Otto Moehrbach]

This macro will do what you want. It will fire whenever an entry is made in
Column B of the active sheet. Note that I named the second sheet "Two".
Place this macro in the sheet module of the sheet that holds your data and
into which the user will make an entry in Column B. To access that module,
right-click on the sheet tab and select View Code. Paste this macro into
that module. "X" out of the module to return to your sheet. This macro
depends on the contents of Column A to determine the extent of your data.
View this message in full screen before you copy to avoid line wrapping.
HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rColA As Range
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
With Sheets("Two")
.Range("A1", .Range("A" & Rows.Count).End(xlUp).Offset(,
1)).ClearContents
Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
rColA.Resize(, 2).AutoFilter
rColA.Resize(, 2).AutoFilter Field:=2, Criteria1:="<>"
rColA.Resize(, 2).SpecialCells(xlCellTypeVisible).Copy
..Range("A1")
End With
End If
End Sub