Re: listing data without blank rows

[Dannycol]

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?



"Aladin Akyurek" wrote:

>
> w1 (source)
>
> Insert 1 row before the data such that A2:D21 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)),"")
>
> Shooter Wrote:
> > My worksheet (w1) has values in cells A1:D20. 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.
> > --
> > Shooter
>
>
> --
> Aladin Akyurek
> ------------------------------------------------------------------------
> Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
> View this thread: http://www.excelforum.com/showthread...hreadid=276804
>
>

[John James]

If you want to delete blank rows on your datasheet, here's a routine 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 try getting the same result but without formulae. For instance if you filter your data on your datasheet, then you'll be able to copy the filtered range onto another sheet. If this meets your needs, you could look at attaching this filter & paste process to a macro/button.

[Dannycol]

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" wrote:

>
> If you want to delete blank rows on your datasheet, here's a routine
> 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 try
> getting the same result but without formulae. For instance if you
> filter your data on your datasheet, then you'll be able to copy the
> filtered range onto another sheet. If this meets your needs, you could
> look at attaching this filter & paste process to a macro/button.
>
>
> --
> John James
> ------------------------------------------------------------------------
> John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
> View this thread: http://www.excelforum.com/showthread...hreadid=532460
>
>

[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 be able to copy that range to your target area.

Cheers,

[Dannycol]

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?


"John James" wrote:

>
> 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 be
> able to copy that range to your target area.
>
> Cheers,
>
>
> --
> John James
> ------------------------------------------------------------------------
> John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
> View this thread: http://www.excelforum.com/showthread...hreadid=532460
>
>