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 Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Format to hide empty rows



 
 
Thread Tools Display Modes
  #1  
Old May 10th 05, 08:47 PM
tamato43
external usenet poster
 
Posts: n/a
Default Format to hide empty rows

Hello All,

Does anyone know how to set a conditional format to hide a colum and/or row
if they are left blank?

Example:

I have a spreadsheet that imports info from another worksheet or file into
10 rows.
In some cases I only populate rows 1 through 5 leaving 6 through 10 blank,
So I'd like to hide those rows.

Please help.
Ads
  #2  
Old May 10th 05, 09:11 PM
Huw Davies
external usenet poster
 
Posts: n/a
Default

I don't think you can use conditional format to automatically hide rows that
are blank. It's either do it by eye using the "hide" option under Format |
Row menu, or by using VBA.

Huw.

"tamato43" wrote:

> Hello All,
>
> Does anyone know how to set a conditional format to hide a colum and/or row
> if they are left blank?
>
> Example:
>
> I have a spreadsheet that imports info from another worksheet or file into
> 10 rows.
> In some cases I only populate rows 1 through 5 leaving 6 through 10 blank,
> So I'd like to hide those rows.
>
> Please help.

  #3  
Old May 10th 05, 09:31 PM
tamato43
external usenet poster
 
Posts: n/a
Default

Do you know of a VBA format then?

"Huw Davies" wrote:

> I don't think you can use conditional format to automatically hide rows that
> are blank. It's either do it by eye using the "hide" option under Format |
> Row menu, or by using VBA.
>
> Huw.
>
> "tamato43" wrote:
>
> > Hello All,
> >
> > Does anyone know how to set a conditional format to hide a colum and/or row
> > if they are left blank?
> >
> > Example:
> >
> > I have a spreadsheet that imports info from another worksheet or file into
> > 10 rows.
> > In some cases I only populate rows 1 through 5 leaving 6 through 10 blank,
> > So I'd like to hide those rows.
> >
> > Please help.

  #4  
Old May 10th 05, 10:13 PM
Nick Hodge
external usenet poster
 
Posts: n/a
Default

The code below finds the last row used in column A and then hides all the
rows in between that have nothing in column A. You should be able to work it
out from there to suit your actual need

Sub HideBlankRows()
Dim lLastRow As Long
lLastRow = Range("A65536").End(xlUp).Row
Range("A1:A" & lLastRow).SpecialCells(xlCellTypeBlanks).EntireRow .Hidden =
True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"tamato43" > wrote in message
news
> Do you know of a VBA format then?
>
> "Huw Davies" wrote:
>
>> I don't think you can use conditional format to automatically hide rows
>> that
>> are blank. It's either do it by eye using the "hide" option under Format
>> |
>> Row menu, or by using VBA.
>>
>> Huw.
>>
>> "tamato43" wrote:
>>
>> > Hello All,
>> >
>> > Does anyone know how to set a conditional format to hide a colum and/or
>> > row
>> > if they are left blank?
>> >
>> > Example:
>> >
>> > I have a spreadsheet that imports info from another worksheet or file
>> > into
>> > 10 rows.
>> > In some cases I only populate rows 1 through 5 leaving 6 through 10
>> > blank,
>> > So I'd like to hide those rows.
>> >
>> > Please help.



  #5  
Old May 10th 05, 10:16 PM
Huw Davies
external usenet poster
 
Posts: n/a
Default

Good point, I sort of left you hanging there didn't I? Please accept my
aplogies. Anyway, the news I have is no better than last time. VBA stands for
Visual Basic for Applications, and is the programming language that sits
behind Excel (and all other MS Office products) that allows you to automate
what Excel does. "Automation" can stretch from the very simple, e.g. "When I
press the button with smiley face, turn all text red", to the horrendously
complex e.g. "Import this table, perform 6 million calculations, produce a
chart, publish it in powerpoint, then send an email, and finally wake me up
when you're done". It's not for the fainthearted, but it can be fun. It would
certainly be possible for it to start at the top of a sheet, and as it works
it's way down, to hide each blank row. I'm afraid I don't have the confidence
or the kind of experience you need to talk you through it on a newsgroup, but
I offer two suggestions. a) if you've time, nip out and get a book on Excel
VBA - not a big thick expensive one, but one for someone brand new to it that
will only cost a few pounds, and / or b) repost your question in the
programming newsgroup with a subject line like "code needed to automatically
hide blank rows" - there's loads of people in there who know all there is to
know about VBA and more importantly, know how to explain it better than I.

I'm really sorry it's not the answer you need, but it's the best I can offer.

Regards,

Huw.

"tamato43" wrote:

> Do you know of a VBA format then?
>
> "Huw Davies" wrote:
>
> > I don't think you can use conditional format to automatically hide rows that
> > are blank. It's either do it by eye using the "hide" option under Format |
> > Row menu, or by using VBA.
> >
> > Huw.
> >
> > "tamato43" wrote:
> >
> > > Hello All,
> > >
> > > Does anyone know how to set a conditional format to hide a colum and/or row
> > > if they are left blank?
> > >
> > > Example:
> > >
> > > I have a spreadsheet that imports info from another worksheet or file into
> > > 10 rows.
> > > In some cases I only populate rows 1 through 5 leaving 6 through 10 blank,
> > > So I'd like to hide those rows.
> > >
> > > Please help.

 




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
can you hide rows with a formula? david Excel Discussion (Misc queries) 2 April 8th 05 03:21 PM
Can Excel "slide up" rows with content thru empty rows to condense portly44 Excel Worksheet Functions 2 April 1st 05 12:47 AM
how to hide rows in a protected sheet Prakash Excel Worksheet Functions 7 January 18th 05 02:42 PM
Empty Cells, Spaces, Cond Format? Ken Excel Discussion (Misc queries) 3 December 4th 04 04:47 PM
Macro to hide rows with empty cells tp58tp Excel Worksheet Functions 2 November 13th 04 02:01 PM


All times are GMT +1. The time now is 02:48 AM.


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