Auto-Delete/Shift Cells Up?

[johnstoll]

I have a spreadsheet containing 3 columns, "A" "B" "C"
Columns "A" and "B" contain unique text
Column "C" contains "yes" or "no"

I made a formula on another spreadsheet in column A (let's call it New A where IF column C on the first spreadsheet says "no", the contents of Column A are copied onto New A

It basically looks like this (assume the column New A is in a separate sheet)

	A	B	C	_	New A
1	text1	textA	yes	_
2	text2	textB	yes	_
3	text3	textC	no	_	text3
4	text4	textD	yes	_
5	text5	textE	no	_	text5
6	text6	textF	no	_	text6
7	text7	textG	yes	_

Now, my question is, can I have excel automatically remove the spaces present in New A and shift all cells up accordingly? If so, how?

[xladept]

The best way is to copy them contiguously onto your New A - post your code and I'll see if I can alter it

[johnstoll]

@xladept
Sorry, but what do you mean by "copy your code"?

[Olly]

To do anything like this 'automatically' in Excel, you need to use VBA. You can't automate deleting rows by just using formulas.

Have you written any VBA code yet? This is what xladept was asking you for...

[Olly]

If you want to delete the entire rows in your second sheet, based on the blanks in column A, try something like this:

Please Login or Register  to view this content.

If you only want to delete the cells in Column A, then try this:

Please Login or Register  to view this content.

[xladept]

post your code and I'll see if I can alter it

This is what I actually said, what I actually meant was post your formula with code tags.

But, Olly should be able to fix you up