Macro to hide blank rows

[Alan Smith]

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 here:

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

 

[Tom Ogilvy]

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

 

[Dave Peterson]

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")).entirerow.hidden = true
end with

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

 

[Alan Smith]

That works great Tom, thanks.

Alan

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