Blank cells, hide row

  • Thread starter Thread starter Murray
  • Start date Start date
M

Murray

Hide i have a piece of code thanks to some newsgroup
people that i have changed a little to suit my purpose.
Basically i want it to look at ColumnB between rows 32 and
79 inclusive and if it fines blank cells in columnB hide
the entire row. This is what i have and it gives me a
compile error Loop without Do. I don't know if the rest
will run because i can't get past the error.
Your help is appreciated
Murray

Sub ActivateNextBlankDown()
ApplicationScreenUpdating = False
ActiveCell.Offset(32, 2).Select
Do While IsEmpty(ActiveCell)
If IsEmpty(ActiveCell) Then
Selection.EntireRow.Hidden = True
Loop
ApplicationScreenUpdating = True
End If
Application.ActivePrinter = "\\SERVER2\Energy3 on Ne10:"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.65)
.RightMargin = Application.InchesToPoints(0.55)
.TopMargin = Application.InchesToPoints(0.58)
.BottomMargin = Application.InchesToPoints(0.57)
.HeaderMargin = Application.InchesToPoints(0.36)
.FooterMargin = Application.InchesToPoints(0.21)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA3
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 75
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub
 
Murray

The problem is that presumably, the cells below B79 are
also empty. I discarded the rest of your program and just
concentrated on this.


Sub ActivateNextBlankDown()
ApplicationScreenUpdating = False
ActiveCell.Offset(32, 2).Select
Do While IsEmpty(ActiveCell)
If IsEmpty(ActiveCell) Then
Selection.EntireRow.Hidden = True
End If
Loop
ApplicationScreenUpdating = True

'Rest of code here


End Sub

This seems to work!

regards
Peter
 
Some of the cells below B79 are blank and some aren't. I
would like the code just concertrate on row 32 to 79.
 
How about something like:

Option Explicit
Sub testme02()
Dim iRow As Long

With ActiveSheet
For iRow = 32 To 79
If IsEmpty(.Cells(iRow, "B")) Then
.Rows(iRow).Hidden = True
End If
Next iRow

End Sub

And if you indent your code a little nicer:

Do While IsEmpty(ActiveCell)
If IsEmpty(ActiveCell) Then
Selection.EntireRow.Hidden = True
Loop
ApplicationScreenUpdating = True
End If

It might make it easier to read and find these kinds of errors.

do/loops and if/end if's come in pairs and can't be mixed like this:

Do While IsEmpty(ActiveCell)
If IsEmpty(ActiveCell) Then
Selection.EntireRow.Hidden = True
end if
Loop

But you'd still have to change the selection (activecell.offset(1,0).select) and
still limit it to rows 32-79.

Another thing that may not work for you, but is good for lots of other stuff is
Data|Filter|Autofilter. You can just show the rows that are non-blank. And if
your data is really nice, you could even just limit it to 32-79. (but that
might be too much to make it useful this time.)
 
Back
Top