listing data without blank rows

[Guest]

My worksheet (w1) has values in cells A120. All cells have a formula or
link to another worksheet. Some of the cells are blank (but still have a
formula in the cell). All of the cells change periodically as I change the
values in other worksheets. In worksheet 2 (w2) A1:a20, I would like to list
all of the values of w1 C1:c20 but without any blank rows. I would like a
formula that will do this automatically. Thanks for your suggestions.

 

[Frank Kabel]

Hi
put the following formula in E1 for example (entered as array formula
with CTRL+SHIFT+ENTER):

=IF(ISERROR(INDEX($C$1:$C$20,SMALL(IF($C$1:$C$20<>"",ROW($C$1:$C$20)),R
OW(1:1)))),"",INDEX($C$1:$C$20,SMALL(IF($C$1:$C$20<>"",ROW($C$1:$C$20))
,ROW(1:1))))

and copy this formula down to D20

 

[Aladin Akyurek]

w1 (source)

Insert 1 row before the data such that A221 houses the data.

In E1 enter: 0
In E2 enter & copy down:

=IF(C2<>"",LOOKUP(9.99999999999999E+307,$E$1:E1)+1,"")

w2 (destination)

In A1 enter:

=LOOKUP(9.9999999999999E+307,Sheet1!E2:E21)

In A2 enter & copy down:

=IF(ROW()-ROW(A$2)+1<=$A$1,INDEX(Sheet1!$C$2:$C$21,MATCH(ROW()-ROW(A$2)+1,Sheet1!$E$2:$E$21)),"")

 

[Guest]

Frank, this works great... thank you.

Hi
put the following formula in E1 for example (entered as array formula
with CTRL+SHIFT+ENTER):

=IF(ISERROR(INDEX($C$1:$C$20,SMALL(IF($C$1:$C$20<>"",ROW($C$1:$C$20)),R
OW(1:1)))),"",INDEX($C$1:$C$20,SMALL(IF($C$1:$C$20<>"",ROW($C$1:$C$20))
,ROW(1:1))))

and copy this formula down to D20

 

[Guest]

Frank,

If I put this formula in worksheet 2 , cell B2, how should I modify the
formula to do the same thing as we did in cell e1 of worksheet 1? Thanks.

 

[Aladin Akyurek]

...this works great....
[...]

put the following formula in E1 for example (entered as arra formula
with CTRL+SHIFT+ENTER):

=IF(ISERROR(INDEX($C$1:$C$20,SMALL(IF($C$1:$C$20<>"",ROW($C$1:$C$20)),R

,ROW(1:1))))

and copy this formula down to D20

Try to insert 1 or 2 rows before the first formula cell. The suggestio
is quite expensive and non-robust (although corrigible)

 

[Frank Kabel]

Hi
try:
=IF(ISERROR(INDEX('sheet1'!$C$1:$C$20,SMALL(IF('sheet1'!$C$1:$C$20<>"",
ROW('sheet1'!$C$1:$C$20)),ROW(1:1)))),"",INDEX('sheet1'!$C$1:$C$20,SMAL
L(IF('sheet1'!$C$1:$C$20<>"",ROW('sheet1'!$C$1:$C$20))

 

[Guest]

thank you.

Hi
try:
=IF(ISERROR(INDEX('sheet1'!$C$1:$C$20,SMALL(IF('sheet1'!$C$1:$C$20<>"",
ROW('sheet1'!$C$1:$C$20)),ROW(1:1)))),"",INDEX('sheet1'!$C$1:$C$20,SMAL
L(IF('sheet1'!$C$1:$C$20<>"",ROW('sheet1'!$C$1:$C$20))

 

[Guest]

Frank,
I am using your formula below and it works well in one of my workbooks. I
have tried the same formula in another workbook and the cells remain blank.
Do you have any "trouble shooting" ideas?
In summary, my data is in worksheet 10, cells aj1:aj200. These cells have
existing simple formulas that are linked to other worksheets. I entered your
formula in worksheet 22, B9. I entered formula as array formula and copied
down to B209. No error messages with the formula but B9:B209 remain blank.
Would you have any suggestions? Thanks.

 

[Frank Kabel]

Hi
sounds like you haven't change all ranges accordingly Post the
formula you have tried

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,
I am using your formula below and it works well in one of my workbooks. I
have tried the same formula in another workbook and the cells remain blank.
Do you have any "trouble shooting" ideas?
In summary, my data is in worksheet 10, cells aj1:aj200. These cells have
existing simple formulas that are linked to other worksheets. I entered your
formula in worksheet 22, B9. I entered formula as array formula and copied
down to B209. No error messages with the formula but B9:B209 remain blank.
Would you have any suggestions? Thanks.

 

[Guest]

Frank,

The details are as follows:
data in worksheet 10 (w10) aj7:aj207. There is also data in columns a-ap
which may not be relevant. In worksheet 22, B9 I have entered the following
formula (array formula):
=if(iserror(index('sheet10'$aj$7:$aj$207,small(if('sheet10'$aj$7:$aj$207<>"",row('sheet10'$aj$7:$aj$207)),row(1:1)))),"",index('sheet10'$aj$7:$aj$207,small(if('sheet10'$aj$7:$aj$207<>"",row('sheet10'$aj$7:$aj$207)),row(1:1)))).
Then I copied down to B209. Thanks for taking another look at this.

 

[Frank Kabel]

Hi
dn't ee an error?. If you like email me this file and I'll have a look
at it

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

The details are as follows:
data in worksheet 10 (w10) aj7:aj207. There is also data in columns a-ap
which may not be relevant. In worksheet 22, B9 I have entered the following
formula (array formula):

=if(iserror(index('sheet10'$aj$7:$aj$207,small(if('sheet10'$aj$7:$aj$20
7<>"",row('sheet10'$aj$7:$aj$207)),row(1:1)))),"",index('sheet10'$aj$7:

 

[Guest]

In reply to Aladin post..

I have used the formula you listed but changed the references etc to suit my
sheet, i have 4 sheets were date is linked with several formula, i was
pleased to see how it worked first time, however my problem is that it has
now slowed down the working of my sheet, E.g when i enter date into some of
the cells it takes 2 whole seconds before i can work in another cell.. i can
only put it down to the number of rows and columns i'm using? I have approx
1600 rows of data most of which are empty but do have formula spread over 10
columns. My aim is to list on a seperate sheet all the rows with data without
the blank rows. As i say everything works fine but sadly slow when entering
data. Any suggestions or ways around it?

 

[John James]

If you want to delete blank rows on your datasheet, here's a routin
which does this:

Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub

If your formulae are slowing down your worksheet, then maybe tr
getting the same result but without formulae. For instance if yo
filter your data on your datasheet, then you'll be able to copy th
filtered range onto another sheet. If this meets your needs, you coul
look at attaching this filter & paste process to a macro/button

 

[Guest]

Thanks for the reply... I’m not clued up on codes or macros so not sure what
to do..

This is my situation.. I have 4 work sheets. In one sheet named 'Claim
Sheet' I have possible data in cells CM10:CV1661 all of these cells have
formulae where the data is automatically entered by me entering data
elsewhere on the same sheet, summarily the data is deleted when required. No
problem here..

What I’m trying to achieve is this.. On a separate work sheet named
'Statement' I need to list all rows with data only from 'Claim Sheet'
CM10:CV1661 excluding all blank rows. The list of rows needs to appear on
'Statement' B10:K25, 16 rows is sufficient as the number of rows with data
from 'Claim Sheet' will vary but never exceed 16.. Data over the 'Claim
Sheet' 1661 rows will be added and deleted periodically but should never
exceed 16 rows at the same time.

Any help would be appreciated.. Is there a code I can just paste into the VB?

As previously mentioned I have achieved the results with formulae but it has
really slowed down the working of the work sheet! So any 'simple' alternative
method would be a great help

 

[John James]

Hi Dannycol,

OK that's clearer. Ignore my previous posting.

Here's one way:

In CW10 enter this formula
=IF(COUNTA(CM10:CV10)=0,0,1)

Copy this formula to the cells in the table below.
Place headings for your data in CM9 to CW9
Whilst in this table range, select Data-Filter-Autofilter
From the drop-down box in CW9 select "1"
Only the non-blank rows in your table will be visible and you'll b
able to copy that range to your target area.

Cheers

 

[Guest]

Hi John

Many thanks for the info.. I'm almost there.. when you say copy this formula
into the table below.. do you mean into CW11:CW1661? this is what i've done.
When selecting auto filter i get drop down lists in all 10 columns..
Selecting '1' from CW9 does not do anything..? however selecting 'none blank'
from one of the other columns does work fine..

The only problem i have (or other users) is having to go thro' auto filter
etc every time the data has changed (added or deleted) in order to view the
completed list without blank rows. I played around with it using custom
option or sorting top 10 hoping i could leave auto filter selected and the
data apearring in the list without blank rows.. any ideas? or have I done
something wrong?

The aim is just to view this sheet with the listed data shown without blank
rows.

Can auto filter be left on without showing the drop down arrows?

 

[Guest]

I have used your formula using 10 columes and 1661 rows and it works fine..
the source data is inserted via formula from other cells, no problem all
works fine, the problem i have is this. when i copy the sheet or save it as a
different name and delete the data so i can start with new data, i find only
2 or 3 colums work the other colums return #Value in the source sheet. Any
help would be appreciated

Regards