Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 28
Default Macro to hide blank rows

Hi,

I have recorded a macro to run the auto-filter process, with the results
being shown on a separate sheet via the camera tool. The whole data range is
300 rows, but most results will be less than 20, though could be all, so for
neatness I am trying to hide the empty rows in the output range.

The problem is that the range to hide has now been "fixed" in the macro (I
used End+Shift+Up Arrow to highlight it). The relevant code is he

Rows("800:800").Select
Range(Selection, Selection.End(xlUp)).Select
Rows("507:800").Select
Range("A800").Activate
Selection.EntireRow.Hidden = True

Is there an easier way to achieve the result i.e. hide the empty rows in the
range 500:800?

Thanks,

Alan
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,953
Default Macro to hide blank rows

Dim rng as Range
On Error Resume Next
Range("A500:A800").EntireRow.Hidden = false
set rng = Range("A500:A800").SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
rng.entirerow.Hidden = true
End if

--
Regards,
Tom Ogilvy


"Alan Smith" wrote:

Hi,

I have recorded a macro to run the auto-filter process, with the results
being shown on a separate sheet via the camera tool. The whole data range is
300 rows, but most results will be less than 20, though could be all, so for
neatness I am trying to hide the empty rows in the output range.

The problem is that the range to hide has now been "fixed" in the macro (I
used End+Shift+Up Arrow to highlight it). The relevant code is he

Rows("800:800").Select
Range(Selection, Selection.End(xlUp)).Select
Rows("507:800").Select
Range("A800").Activate
Selection.EntireRow.Hidden = True

Is there an easier way to achieve the result i.e. hide the empty rows in the
range 500:800?

Thanks,

Alan

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Macro to hide blank rows

range("500:800").entirerow.hidden = true

or if you really want to start from the bottom and hide the unused rows.

dim FirstRowToHide as range
with activesheet
set firstrowtohide = .cells(.rows.count,"A").end(xlup).offset(1,0)
.range(firstrowtohide,.cells(.rows.count,"A")).ent irerow.hidden = true
end with

it hides all the rows below the last used row (not stopping with 800).

Alan Smith wrote:

Hi,

I have recorded a macro to run the auto-filter process, with the results
being shown on a separate sheet via the camera tool. The whole data range is
300 rows, but most results will be less than 20, though could be all, so for
neatness I am trying to hide the empty rows in the output range.

The problem is that the range to hide has now been "fixed" in the macro (I
used End+Shift+Up Arrow to highlight it). The relevant code is he

Rows("800:800").Select
Range(Selection, Selection.End(xlUp)).Select
Rows("507:800").Select
Range("A800").Activate
Selection.EntireRow.Hidden = True

Is there an easier way to achieve the result i.e. hide the empty rows in the
range 500:800?

Thanks,

Alan


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 28
Default Macro to hide blank rows

That works great Tom, thanks.

Alan

"Tom Ogilvy" wrote:

Dim rng as Range
On Error Resume Next
Range("A500:A800").EntireRow.Hidden = false
set rng = Range("A500:A800").SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
rng.entirerow.Hidden = true
End if

--
Regards,
Tom Ogilvy


"Alan Smith" wrote:

Hi,

I have recorded a macro to run the auto-filter process, with the results
being shown on a separate sheet via the camera tool. The whole data range is
300 rows, but most results will be less than 20, though could be all, so for
neatness I am trying to hide the empty rows in the output range.

The problem is that the range to hide has now been "fixed" in the macro (I
used End+Shift+Up Arrow to highlight it). The relevant code is he

Rows("800:800").Select
Range(Selection, Selection.End(xlUp)).Select
Rows("507:800").Select
Range("A800").Activate
Selection.EntireRow.Hidden = True

Is there an easier way to achieve the result i.e. hide the empty rows in the
range 500:800?

Thanks,

Alan

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
Macro to HIDE blank rows JForsyth Excel Worksheet Functions 6 June 5th 09 04:51 PM
Hide Blank Rows TamIam Excel Worksheet Functions 3 May 6th 09 08:42 PM
hide blank rows violet Excel Programming 3 November 9th 06 10:20 AM
Hide Blank Rows dee Excel Worksheet Functions 2 May 17th 06 11:30 PM
macro to hide rows if cell is blank Shooter Excel Worksheet Functions 3 September 28th 05 10:55 PM


All times are GMT +1. The time now is 02:44 PM.

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"