Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide columns macro
I want to write a macro which hides columns if the value in the bottom row of
the column is 0. Can anyone help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide columns macro
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" wrote in message ... I want to write a macro which hides columns if the value in the bottom row of the column is 0. Can anyone help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide columns macro
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" wrote in message ... 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" wrote in message ... I want to write a macro which hides columns if the value in the bottom row of the column is 0. Can anyone help? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide columns macro
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" wrote in message ... I want to write a macro which hides columns if the value in the bottom row of the column is 0. Can anyone help? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide columns macro
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide columns macro
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" wrote in message ... I want to write a macro which hides columns if the value in the bottom row of the column is 0. Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Hide columns | Excel Discussion (Misc queries) | |||
Need a macro to hide certain columns | Excel Discussion (Misc queries) | |||
macro to hide columns | Excel Worksheet Functions | |||
Macro to hide or unhide columns | Excel Programming | |||
Macro to hide Columns | Excel Programming |