hide columns macro

[xkarenxxxx]

I want to write a macro which hides columns if the value in the bottom row of
the column is 0.
Can anyone help?

[Jeff Standen]

For a = 0 To 255 'Change this if you don't need it to do every column if the
sheet
If Range("a65536").Offset(0, a).Value = "0" Then
Range("a65536").Offset(0, a).EntireColumn.Hidden = True
Next a


"xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> wrote in message
news:F9C90AD5-DE2A-46B7-96AE-D069007A16CD@microsoft.com...
>I want to write a macro which hides columns if the value in the bottom row
>of
> the column is 0.
> Can anyone help?

[Jeff Standen]

There isn't supposed to be a line break after Then - if you do have one you
will need an END IF after the next line.

Jeff

"Jeff Standen" <jeff@work.com> wrote in message
news:ekvz%234lhGHA.4892@TK2MSFTNGP02.phx.gbl...
> For a = 0 To 255 'Change this if you don't need it to do every column if
> the sheet
> If Range("a65536").Offset(0, a).Value = "0" Then
> Range("a65536").Offset(0, a).EntireColumn.Hidden = True
> Next a
>
>
> "xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> wrote in message
> news:F9C90AD5-DE2A-46B7-96AE-D069007A16CD@microsoft.com...
>>I want to write a macro which hides columns if the value in the bottom row
>>of
>> the column is 0.
>> Can anyone help?
>
>

[Bob Phillips]

Sub HideColumns()
Dim i As Long
Dim j As Long
With ActiveSheet
For i = 1 To .Columns.Count
j = .Cells(.Rows.Count, i).End(xlUp).Row
If Not IsError(.Cells(j, i).Value) Then
.Columns(i).Hidden = .Cells(i, j).Value = 0
End If
Next i
End With
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> wrote in message
news:F9C90AD5-DE2A-46B7-96AE-D069007A16CD@microsoft.com...
> I want to write a macro which hides columns if the value in the bottom row
of
> the column is 0.
> Can anyone help?

[Tom Ogilvy]

another possibility

in case you meant the last entry in the column rather than literally the
65356th row, then

Dim i as Long, lastcol as Long
Dim rng as Range
With ActiveSheet.UsedRange
lastcol = .Columns(.columns.count).Column
End with
With Activesheet
for i = 1 to lastcol
set rng = .cells(.rows.count,i).End(xlup)
if isnumeric(rng) and rng <> "" then
if rng = 0 then
.columns(i).Hidden = True
end if
end if
Next
End With

--
Regards,
Tom Ogilvy

"xkarenxxxx" wrote:

> I want to write a macro which hides columns if the value in the bottom row of
> the column is 0.
> Can anyone help?

[Norman Jones]

Hi Xkarenxxxx,

Try,

'=============>>
Sub Tester()
Dim SH As Worksheet
Dim col As Range
Dim LastCell As Range

Set SH = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE

For Each col In SH.UsedRange.Columns
Set LastCell = Cells(Rows.Count, col.Column).End(xlUp)
col.Hidden = LastCell.Value = 0
Next col
End Sub
'<<=============


---
Regards,
Norman


"xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> wrote in message
news:F9C90AD5-DE2A-46B7-96AE-D069007A16CD@microsoft.com...
>I want to write a macro which hides columns if the value in the bottom row
>of
> the column is 0.
> Can anyone help?