VBA to Copy Non-Blank Cells

[jennyaccord]

Please help, I'm kind of stuck.

If you open the attached spreadsheet you should see that on the sheet named Test, several columns have "_AD" in the header and every row only has a single entry in a column with "_AD" at the top, i.e. John has an entry in "One_AD", Steve has an entry in "Three_AD", etc.

What I need to do is select column A and only those columns with "_AD" in the header (which I can do with a simple Select macro as their positions won't change), then copy the information into a new sheet, but copy only those cells containing information, so that I end up with information that looks like the sheet named Result.

Any help would be greatly appreciated.

[StephenR]

Where should the results of the other columns go?

OK scratch that, I see everything goes in one column.

[jennyaccord]

Yeah. I have an info dump from an online questionnaire to deal with and one of the questions has sub-questions (1, 2 or 3 sub-questions depending on the original answer given) and I only need one specific sub-answer from each, however the columns containing the answer I need aren't always in the same place (as demonstrated in the example I attached), but they do have headers containing identifying information I can exploit, I just don't know how.

[StephenR]

Perhaps this?

Please Login or Register  to view this content.

[jennyaccord]

Stephen

Thanks for your reply and apologies for my lateness in responding (Easter and wotnot).

The code you gave me works beautifully on the test document I attached, doing everything I needed. However, when I tried to use it with my live document (which is a much bigger document) something seems to be going awry.

It does pull through some information, however it appears to only be reading the first column containing "_AD" in the header and deleting subsequent the rows between where the first column headed with "_AD" contains blanks. Meaning that I'm only getting a portion of the data. It then gives me a 1004 runtime error.

As an example from the test document (and keep in mind that the code you gave DOES work for the test document, I'm only using it as an example) I'm only getting results for John and Gerry (the rest are missing) and there is no blank row between the 2 entries.

Did that make sense?

[StephenR]

Not entirely Jenny. Can you post a small sample of your actual data?

[jennyaccord]

Sure. I've anonymised it as best I can, though the headers and directorates/service areas are as they appear on the live data.

If you run the code you'll see that all you get is data from the first column with "_AD" in the header.

There is probably something ridiculously easy that I'm missing, but I can't figure it out. As I said, I'm not good with VB.

Thanks for your help.

[StephenR]

Try this:

Please Login or Register  to view this content.

[jennyaccord]

Thanks everso much Stephen.

It took me a while to work out what was wrong as it kept bringing across information from other fields too, but that was a mistake on my part as I didn't read my column headers properly and there are several other headers that contain "_AD" such as "_address....".

D'oh.

Anyway, I've adjusted the criteria and it now works brilliantly.

Now, all I have to do is connect it to the select query and I'm set.

Thanks again.

[StephenR]

Glad to hear it.

"_AD" such as "_address...."

You could get round that by changing the matchcase parameter to True.

[jennyaccord]

Thanks for all your help Stephen.

Sorry it's taken so long to update this thread to 'Solved'.