• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete blank rows

H

hendis

New Member
Hi, everybody!


Please point me in the right direction.


I've got a 70-row X 6-column array (A60 to F129). I need to automatically delete all of the rows where all 6 cells are blank. I've usually got only 6-10 rows in the array that have data. (never more than 18 rows).


I need to reduce the size of the array to show only the rows that have data.

I need to do this automatically -- I don't want any user action to be required.

The resulting array should not return errors for any reason.


I need to place the resulting array in another worksheet named "Summary".


I can't figure out how to do this. A regular or array formula is OK, as is a VBA procedure. (FWIW, I'm a newbie regarding VBA, so if it goes this route, I'll need some hand-holding. Note: Be prepared for some dumb questions.)


Thanks,


hendis
 
Hendis


Firstly, Welcome to the Chandoo.org forums.


Is it possible for you to share with us either the file or the data in the area with an explanation of what your doing with it and why you need to delete the rows.


It may well be possible to write a formula to ignore the blanks if we know what your trying to do


You can post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi


I assume A60:F60 hold column headers.


In H61


=COUNTA(A61:F61)>0


Now activate Summary Sheet > Goto Data > Advanced Filter > Check 'Copy to another location' > List range: Select the actual range Sheet1!A60:F129 > Criteria Range: Sheet1!H60:H61 > Copy to A1 or wherever you want to paste the data > OK.


replace the 'Sheet1' from Sheet1!A60:F129 with the actual sheet name


You could record a macro for this action.


Kris

Kris
 
hendis,

Think your data from A1:I70

1. At G1 Write =COUNTA(A1:F1)

2. Select Column G

3. Ctrl+F > Option > Select Values from Look in > Find All > Ctrl+A

4. Close Find dialog box

5. Ctrl+- or Alt+H+D+R

Done!


Regards,


--Muneer
 
Hi Hendis,


Can you please download the below file and check. (Use Control+q to run the report)


http://www.2shared.com/document/7ULlVzEB/Book7.html


Thanks,

Suresh Kumar S
 
Sorry that it's taken so long for me to get back to you... (work)


I feel dumb saying this, but I have an example in my Google Drive -- now how do I share it with everybody?


hendis
 
You must log in or register to reply here.
Back
Top