print macro
Hi,
if I have a similar problem I use two approaches (described only for
rows, can be used for columns as well):
1) in a verly left column (which can be hidden) I write a formula which
checks the content of the relevant columns on the right hand side
(=if(...)). When the appropriate column should be visible a value is
given by the formula, if not "hidden" will be given. Then I have some
VBA code hiding the rows:
Dim rngArea As Range
Application.ScreenUpdating = False
ActiveSheet.Range("a16: a306").Select
Selection.SpecialCells(xlCellTypeFormulas, 2).Select
For Each rngArea In Selection
rngArea.EntireRow.Hidden = True
Next
2) When I have a block of data where I want to hide the lines with or
without any entry I use the following macro:
Sub HideEmptyRows()
Dim AktZ As Integer 'counter of row
Dim AktSp As Integer 'counts column
Dim StaSp As Integer 'Starting column
Dim StaZ As Integer 'Start row
Dim EndZ As Integer 'end row
Dim Inhalt As String 'content or no content
Dim i As Integer 'Standard counter
Dim Box As String
Dim Botschaft As String
Dim Abfrage As String
Dim ZU As String
'Information box
ZU = Chr(10)
Botschaft = "This routine is used to hide rows in a list,"
+ ZU + _
"where in the relevant is either nothing or something" +
ZU + ZU + _
"For this, the cursor has to be put into the upermost and
leftmost cell of the list." + ZU + ZU + _
"If this is correct, verify with 'Yes' otherwise cancel
with 'No'"
Box = MsgBox(Botschaft, vbYesNo, "Information")
If Box = vbNo Then
Exit Sub
End If
StaZ = Selection.Row
AktSp = Selection.Column
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown inserts supportive row
Cells(StaZ, 1).Select
For i = 1 To 25
Cells(StaZ, i).Select
Selection.Value = i 'includes numbers for
orientation
Next i
Cells(StaZ, AktSp + 1).Select
Botschaft = "Please enter the number of the relevant row"
+ ZU + _
"(see supportive numbering just obove the list)"
StaSp = InputBox(Botschaft, "Query for relevant column")
Botschaft = "If you want to hide rows which have a value
in the relevant column," + ZU + _
"please click on 'Yes', " + ZU + _
"if you want to hide those, where the relevant column is
empty, click on 'No'."
Inhalt = MsgBox(Botschaft, vbYesNo, "Query for content")
'result is no content = vbNo or content = vbYes
Application.ScreenUpdating = False
Selection.EntireRow.Delete 'removes row with
supportive numbers
Cells(StaZ, AktSp).Select
Selection.End(xlDown).Select
EndZ = Selection.Row
Cells(StaZ, StaSp).Select
AktZ = StaZ
Do Until AktZ EndZ
Select Case Inhalt
Case vbNo
If Selection.Value = "" Then
Selection.EntireRow.Hidden = True
End If
Case vbYes
If Selection.Value < "" Then
Selection.EntireRow.Hidden = True
End If
End Select
AktZ = AktZ + 1
Cells(AktZ, StaSp).Select
Loop
Application.ScreenUpdating = True
End Sub
Hope this helped.
Udo
|