Cell Macro

  • Thread starter Thread starter Eilidh
  • Start date Start date
E

Eilidh

I'm creating a stationery order form as a spreadsheet
which uses the "IF" function to decide which items go on
the final form. I need a way to hide the cells which
aren't being used or move all the used cells to the top of
the form, because at the moment Excel is printing out 4
pages of boxes when only one item is being ordered. Can
anyone help?

Thanks,
Eilidh
 
Eilidh

This should do it:

Sub HideRows()
Dim rng As Range
Set rng = Range("B1:B400") 'alter this range to suit the
Qty range
rng.Select
'Make sure all row are visible
Selection.EntireRow.Hidden = False

For Each c In rng
'make sure that column A has data and Oty is empty
'to stop infinite looping
If IsEmpty(c) And c.Offset(0, -1) <> "" Then
c.EntireRow.Hidden = True
End If
Next c

End Sub

Regards
Peter
 
Back
Top