A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Hide Blank Rows



 
 
Thread Tools Display Modes
  #1  
Old May 17th 06, 10:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Hide Blank Rows

Hi there,

Does this make sense?

Situation: Receive database in client format. I insert a key column,
number it sequentially so that I can put it back in order after manipulating
the data. I then add a formula on each row, example:
=IF(COUNTA(b3:m3)=0,"blank row","")

I then sort by this column to put all "blank row" rows together, copy and
paste values into another worksheet, fill in pricing, etc., then copy the
data back into the original worksheet.

After this, I re-sort by the key column, delete the column with the If
statement above and the delete the key column to put the data back as sent.

Does anyone see a problem with doing it this way? I know I can create a VB
macro, but we are trying to avoid this for various reasons.

Thanks!

--
Thanks!

Dee
Ads
  #2  
Old May 17th 06, 11:19 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Hide Blank Rows

Is there a reason you copy to a different worksheet?

And if you could pick out a column that always has data on it if that row is
used, maybe you could just apply data|Filter|autofilter to that column. Then
filter to show the blanks and delete the visible rows. Then remove the filter.

This can have trouble under some circumstances. I'd do the the same thing as
you but using the original worksheet--with one additional step. I'd convert
that formula that returns "blank row" to values before I sorted.

The fewer formulas, the faster things work.



dee wrote:
>
> Hi there,
>
> Does this make sense?
>
> Situation: Receive database in client format. I insert a key column,
> number it sequentially so that I can put it back in order after manipulating
> the data. I then add a formula on each row, example:
> =IF(COUNTA(b3:m3)=0,"blank row","")
>
> I then sort by this column to put all "blank row" rows together, copy and
> paste values into another worksheet, fill in pricing, etc., then copy the
> data back into the original worksheet.
>
> After this, I re-sort by the key column, delete the column with the If
> statement above and the delete the key column to put the data back as sent.
>
> Does anyone see a problem with doing it this way? I know I can create a VB
> macro, but we are trying to avoid this for various reasons.
>
> Thanks!
>
> --
> Thanks!
>
> Dee


--

Dave Peterson
  #3  
Old May 17th 06, 11:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Hide Blank Rows

Hi and thanks for your response.

It is done on another sheet as it must then be uploaded into another system
that will input pricing automatically and it must be formatted in a specific
fashion. It then must be put back into the original worksheet and made to
look exactly as it was received before sending back to the client.

I like the extra idea of removing the formulas. Thanks - I will implement
it. Any other suggestions are welcome.

--
Thanks!

Dee


"Dave Peterson" wrote:

> Is there a reason you copy to a different worksheet?
>
> And if you could pick out a column that always has data on it if that row is
> used, maybe you could just apply data|Filter|autofilter to that column. Then
> filter to show the blanks and delete the visible rows. Then remove the filter.
>
> This can have trouble under some circumstances. I'd do the the same thing as
> you but using the original worksheet--with one additional step. I'd convert
> that formula that returns "blank row" to values before I sorted.
>
> The fewer formulas, the faster things work.
>
>
>
> dee wrote:
> >
> > Hi there,
> >
> > Does this make sense?
> >
> > Situation: Receive database in client format. I insert a key column,
> > number it sequentially so that I can put it back in order after manipulating
> > the data. I then add a formula on each row, example:
> > =IF(COUNTA(b3:m3)=0,"blank row","")
> >
> > I then sort by this column to put all "blank row" rows together, copy and
> > paste values into another worksheet, fill in pricing, etc., then copy the
> > data back into the original worksheet.
> >
> > After this, I re-sort by the key column, delete the column with the If
> > statement above and the delete the key column to put the data back as sent.
> >
> > Does anyone see a problem with doing it this way? I know I can create a VB
> > macro, but we are trying to avoid this for various reasons.
> >
> > Thanks!
> >
> > --
> > Thanks!
> >
> > Dee

>
> --
>
> Dave Peterson
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Blank Rows from Merged Cells in Drop Down Menu Kati Excel Discussion (Misc queries) 1 February 20th 06 07:59 PM
Macro to hide blank cells in a range Dave Excel Discussion (Misc queries) 1 February 1st 06 11:55 PM
Can I develop a macro to add blank rows to a list in Excel? csimont Excel Discussion (Misc queries) 1 January 18th 06 02:46 PM
Hide rows but show when printing dyukon Excel Discussion (Misc queries) 1 January 16th 06 05:08 PM
how to hide rows in a protected sheet Prakash Excel Worksheet Functions 7 January 18th 05 02:42 PM


All times are GMT +1. The time now is 04:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.