Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

On Sheet 1 I have a list of expenditures in column C, some of which are paid out

of a grant. In column B there are index numbers which identify from which budget

the expenditure is coming.

I want to list all the expenditure from the grant, and only that expenditure, on

a separate sheet. I can do this by a simple if formula (which even I

understand!): =IF(B1="BH",C1," ")

On my new sheet however I then have big gaps between the entries. I assume it is

possible to write a VBA loop which would copy only the relevant items and make a

neat list. Is this my best bet?

I also wondered whether it would be possible to use an array formula to do this

job.

Any thoughts on either option gratefully received.

If you only need to do this occasionally I suggest selecting the

column with the big gaps, including the header and in the drop down menu :

Data|Filter|Autofilter

Then in the dropdown list which appears in the header cell choose

'(Non Blanks)'. This will hide all the rows with blanks in. Copy the

column to somewhere else in the sheet, or another sheet, in any event

avoid copying it on top of the hidden rows. Incidentally, it might be

safer to have no space between the quote marks at the end of your formula.

I'm actually setting this up for someone else, who is very nervous about

using the computer at all. I'm therefore trying to make it as automated as

possible for her.

Thanks also for the comment about the blank spaces between the quotes: is

there a quick explanation as to why this is better?

"is there a quick explanation as to why this is better?"

Note I did say 'might be safer' and that it was an incidental comment.

I just like things to be what they seem. As much as possible if a cell

looks empty I like it to be empty so that I don't have to start

considering 'What if there are invisible characters in there - how

shall I handle them? How many invisible characters are there in that cell?

The procedure I described using '(Non Blanks)' treats cells with just

a space in, in the same way as empty cells. So it doesn't matter there.

It is just a matter of preference.

If I get a moment I will address your chief problem.

>>If I get a moment I will address your chief problem.

I've now gone with my original messy solution, as I had to get it done, but

thanks anyway for your interest and time.

Didn't find what you were looking for?
Find more on VBA or array formula?
Or get search suggestion and latest updates.

Related Topics:

- VBA Formula prob
- Getting a formula to copy from inside VBA
- Formula using range name in VBA code
- VBA code to add values in two cells which have formulas
- formula help but not vba please
- Copy down formula with vba
- cells containing formula that refer to user-defined VBA function
- VBA Code for pasting a set of formulas down a data range
- VBA variable range in a formula
- Array Formula
- formula returns to multiple cells, without Array object?
- Using an array formula as a hyperlink cell reference
- VBA Array - return value to a cell
- sorting an array in VBA without writing it in excel sheet
- VBA and Arrays/Ranges
- Multidimensional array problem in Excel VBA
- Placing VBA Code "Live" Into Another WkBook Via VBA
- writing vba code to generate vba code
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Convert code from excel 2000 vba to excel 2003 vba
- writing vba code to generate vba code
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Array to array direct transfer in VB
- Array of Arrays
- clearing cells value without deleting the formula I've made in cell from excel formula , HOW?