Hiding rows with zero values

[scottkrise05]

I really liked this example because it is extremly effecient when
hiding zero values in a given column, but I was wondering if I could
get some help in modifying this example for when the column is unknown
but has been selected. The reason I ask is I would like to use this
feature on several different spreadsheets, but I would like to be able
to use the same macro without having to modify it each time.

Thank you!!!!

Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range


With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With


With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in
the cell
ElseIf .Cells(Lrow, "A").Value = "0" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A")
Else
Set rng = Application.Union(rng, .Cells(Lrow,
"A"))
End If
End If
Next
End With
'hide all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Hidden = True


With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[Gary Keramidas]

try adding these 2 lines:

Dim colnum As Long
colnum = ActiveCell.Column

then where you explicitly use column "A":
(Lrow, "A")

use:
(Lrow,colnum)

give that a try

 

[Bob Phillips]

Sub UnionExample()
Dim Lrow As Long
Dim LCol As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range
Dim cell As Range

Set cell = Application.InputBox("Select target column with mouse",
Type:=8)
If Not cell Is Nothing Then

LCol = cell.Column

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With


With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, LCol).Value) Then
'Do nothing, This avoid a error if there is a error in
the cell
ElseIf .Cells(Lrow, LCol).Value = "0" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, LCol)
Else
Set rng = Application.Union(rng, .Cells(Lrow, LCol))
End If
End If
Next

'hide all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End If
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)