Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to HIDE blank rows | Excel Worksheet Functions | |||
Hide Blank Rows | Excel Worksheet Functions | |||
hide blank rows | Excel Programming | |||
Hide Blank Rows | Excel Worksheet Functions | |||
macro to hide rows if cell is blank | Excel Worksheet Functions |