OK...

From the "Whenever possible, let Excel do the work instead of a Macro"
department:

Goal: All the unanswered questions  in another list  with no blank lines.

As I believe you describe, a macro *could* be written to scan through the
questions and
store the un-answered ones in the other sheet  in sequential cells.

However, how about simply sorting the questions by your "Answered Flag" ...
that's
whatever you mentioned that you can make True or False depending on whether a q
is
answered.
You may need to copy the full list then sort.

I have sorted lists (with screen updating off) done  things as desired, then
"Un-Sorted" it
back to orginal. I add a column containing sequential numbers, to the original
list, to sort
on for the "Un-Sort" to go back to original. I also Named the Range containing
the list.

I have also used command buttons at the top of colums to sort the respective
columns.

I even wrote it so the cell selected after the sort is the same one selectd
before the sort,
so the user is in the "same place" (contents-wise).

Thanks for the idea...but...this is a user checklist - others will be
using the checklist for projects (making a copy of my file to save
for their projects). So I need to be able to automate it as much as
possible and get the list  of missing/unanswered questions  on one
sheeet in a short simple list that then can be emailed out
automatically to managers to update them on the project's progress.
I have done  a worksheet with all the questions and whether or not
they have been answered but I would like to shorten it up so the user
can just look at the unanswered questions and not have to scan the
entire list. And I can't sort the checklist - there are control
buttons, textboxes, etc. all over the sheet. The checklist also has
links to other worksheets that are initiated when a user clicks a
OptionButton. Eventually the checklist will be a vb form with tabs
for the five sections but I didn't have the time to get my skill
level up on forms in time to meet the deadline for a checklist so I
put it in an excel sheet.

If there is a way to attach a sample of my workbook to show you what
I have going on let me know - I am new to this group and clueless
about what and how I can send attachments if at all!

This isn't pretty, but it may give you an idea of what to do. I've
placed it in the workbook before closing event

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'constants
'constants
HomeQCell = "A3" 'question
HomeACell = "B3" 'answer
HomeMCell = "A1" 'missing
QSheet = "Sheet1"
MissingAnsSheet = "Sheet2"


'turn off so not prompted to 'resave' book
Application.DisplayAlerts = False

'save user position
UserSheet = ActiveSheet.Name
UserCell = ActiveCell.Address
TheVertScroll = ActiveWindow.ScrollRow
TheHorzScroll = ActiveWindow.ScrollColumn

'determine where to grab
Sheets(QSheet).Activate
Range(HomeQCell).Select
'find last filled row
ThisCell = -1 'dummy
While ActiveCell.Address <> ThisCell
ThisCell = ActiveCell.Address
Selection.End(xlDown).Select
Wend
Selection.End(xlUp).Select
LastQCell = ActiveCell.Address
NumQs = Range(LastQCell).Row - Range(HomeQCell).Row + 1

'place data into arrays
QCol = Range(HomeQCell, LastQCell).Value
LastACell = Cells(Range(HomeACell).Row + NumQs - 1,
Range(HomeACell).Column).Address
ACol = Range(HomeACell, LastACell).Value

'count number of FALSE
NumFalse = Application.CountIf(Range(HomeACell, LastACell), False)

'put Q of FALSE A into array ...
If NumFalse <> 0 Then
ReDim MissingA(1 To NumFalse, 1 To 1)
cnt = 0
For i = 1 To NumQs
If ACol(i, 1) = False And Not IsEmpty(ACol(i, 1)) Then
cnt = cnt + 1
MissingA(cnt, 1) = QCol(i, 1)
End If
Next i
'... put into sheet
Sheets(MissingAnsSheet).Activate
Range(HomeMCell).Resize(NumFalse, 1).Value = MissingA
End If

'restore userposition
Sheets(UserSheet).Activate
Range(UserCell).Select
ActiveWindow.ScrollRow = TheVertScroll
ActiveWindow.ScrollColumn = TheHorzScroll

'save book
ActiveWorkbook.Save

'restore warnings
Application.DisplayAlerts = True
End Sub

Unfortunately, reading others code is difficult because they
frequently use many methods I am unfamiliar with. I prefer a concise
explaination (pseudo code if you prefer) of the basic algorithm (such
as Dave's comments). ..However.

I suggested "collecting" the UnAnswered Q's by sorting _a_ list
in a sheet. I tried to point out that you don't have to sort the
actual list  that is used.
You can copy the list to another (possibly hidden) sheet  and (have
Excel) sort the copy for you.
I also suggested sorting the list, collecting the unanswered ones
(to another sheet), then "Un Sorting" back to the original order.
[This last may seem potentially distructive, thus less desirable].
Snippets below...

It appears that Dave is saving the unanswered questions  to another
sheet via an array. If I understand Dave's algo, he gathers all the
Q's in the array, then only saves the unanswered ones to the
"Missing-Ans-Sheet".
I think you could also collect ONLY the Unanawered Q's in the array
in the first place, then dump the entire array in the
"Missing-Ans-Sheet".

No?

After I sent the code,I thought about it some more and would've
changed a number of things - and I also had a d'oh moment when I
registered all of what you'd suggested in the first place: using
Excel's Sort on the pertinent data Copied and Pasted into another
sheet (I like it).

As to only grabbing only unanswered questions  in the first place: this
was perhaps a bit of 'do now, think later' on my part. I'm currently
dealing with spreadsheet 'datasets' of up to 20,000 rows, so it's
become a habit of mine to stick stuff in arrays. My thought process
was that one has to check if the question was answered at some point,
either before it's put into an array or after. If before, I think that
would mean that you'd have to move down the column cell by cell,
checking for whether it's been answered (True or False). This
'cell-by-cell' process is slow, compared to performing it when the
data is in an array (hence, 'stick it into an array'). For this
problem, of 100 or so questions, I'd be very much surprised if this
method is any slower than using an array.

I certainly have very limited experience in VBA programming, so if
there's a method to loading an array with only data meeting particular
criteria, not involving a programic inspection of each cell, I'd be
interested in what it is!

> ... I also had a d'oh moment when [snip]

Sometimes the best moments. I have a somewhat related saying:

"They say we learn by our mistakes. BOY! am I learning now!"



> As to only grabbing...unanswered questions  in the first place:
> this was perhaps a bit of 'do now, think later' on my part. ...
> it's become a habit of mine to...

No prob. as they say. If it's comfortable (and works), do it.
Software is like that.



> ...one has to check ... either before ... or after.
> If before,...This...is slow, compared to ...data...
> in[to] an array (hence, 'stick it into an array').

I was thinking it would take less time to only load the
unanswered, a subset... Cuz...I assumed you *were* doing a
cell-by-cell loop... (:-(
I gather that there is an "official" VBA *Method* which loads an
array from, I can guess, a range of cells. BAM! I may have seen this
in my prowlings for VBA knowledge, but skipping it.


> I certainly have very limited experience in VBA programming,

No shame in that. Same here. I'm an RF Engineer turned, well,
lots of things. Manager, Programmer, laser show designer, home
builder, Self educated as well on VBA... I've been at VBA only abt 4
years, but Excel macros for a while. I did quite a bit at work, but
now control my Ham radios via serial port using Excel macros. Pretty
neat. Click and the radio tunes to a frequency, or uploads /
downloads several hundred memories. I may, some day, do Doppler
correction for talking through an Amateur Radio Satellite - we have a
couple dozen up there.

Lots of "Google-Time", lots of confusion trying to decode the
software-eese explanations.

Whatever works for you is just fine, I was only suggesting
alternatives, but you're ahead of me on this one.

> ...loading an array with only data meeting particular criteria...

Not'a clue..

I recorded a macro that copied 3 columns that I needed from the
checklist sheet  ("Sheet1"), went to another sheet ("Sheet2"), Paste/
Special/Value the copied cells to Sheet2 then sorted them so the
FALSE rows that I did an If formula to show blank if FALSE would sort
to the bottom of the list. This worked fine in the macro but when I
copied the macro code to the Command Button that I wanted it to run
from I get: Run Time Error '1004' Method 'Range'
of 'object_Worksheet' Failed

Here is the code in VBA:

Private Sub CommandButton1_Click()
Range("D4:F23").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks
:=False, Transpose:=False
Range("A2:C21").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range
("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
End Sub

It stops at: Range("D4:F23").Select

I thinkl you gotta place the statement Worksheets("Sheet1").Select before the
Range("D4.....).select statement.