Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
tamato43
 
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.
  #2   Report Post  
Huw Davies
 
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   Report Post  
tamato43
 
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   Report Post  
Nick Hodge
 
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
...
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   Report Post  
Huw Davies
 
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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 03:42 PM
Empty Cells, Spaces, Cond Format? Ken Excel Discussion (Misc queries) 3 December 4th 04 05:47 PM
Macro to hide rows with empty cells tp58tp Excel Worksheet Functions 2 November 13th 04 03:01 PM


All times are GMT +1. The time now is 11:33 AM.

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

About Us

"It's about Microsoft Excel"