Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Columns if ..................
I have been using a hide rows macro from Frank Kabel
succesfully for a long time. I now have use for this macro as Hide Columns (instead of rows) I used Franks macro and tried to replace row references to columns but end up with Run time error on the most important line. Can someone help? Option Explicit Sub Hide_Columns() ' Hide_Columns Macro ' December 7, 2005 ' "transpose" from Frank Kabels Hide Rows Macro ' Keyboard Shortcut: NONE ActiveSheet.Unprotect Password:="XYZ" ' Dim RowNdx As Long Dim ColumnNdx As Long ' Dim lastrow As Long Dim LastColumn As Long Application.ScreenUpdating = False ' lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row ' THE FOLLOWING comes up with Runtime error 1004 ' Application defined or object defined error ' I have no idea what this means, other than something is wrong. LastColumn = ActiveSheet.Cells(Columns.Count, "1").End(xlLeft).Column ' For RowNdx = lastrow To 1 Step -1 For ColumnNdx = LastColumn To 1 Step -1 ' If Cells(RowNdx, "B").Value = "x" Then ' Rows(RowNdx).Hidden = True If Cells(ColumnNdx, "1").Value = "x" Then Columns(ColumnNdx).Hidden = True End If ' Next RowNdx Next ColumnNdx Application.ScreenUpdating = True ActiveSheet.Protect Password:="XYZ" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Columns if ..................
BEEJAY,
Try this Sub Hide_Columns() ' Hide_Columns Macro ' December 7, 2005 ' "transpose" from Frank Kabels Hide Rows Macro ' Keyboard Shortcut: NONE ActiveSheet.Unprotect Password:="XYZ" Dim ColumnNdx As Long Dim LastColumn As Long Application.ScreenUpdating = False LastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column For ColumnNdx = LastColumn To 1 Step -1 If Cells(1, ColumnNdx).Value = "x" Then Columns(ColumnNdx).Hidden = True End If Next ColumnNdx Application.ScreenUpdating = True ActiveSheet.Protect Password:="XYZ" End Sub -- HTH RP (remove nothere from the email address if mailing direct) "BEEJAY" wrote in message ... I have been using a hide rows macro from Frank Kabel succesfully for a long time. I now have use for this macro as Hide Columns (instead of rows) I used Franks macro and tried to replace row references to columns but end up with Run time error on the most important line. Can someone help? Option Explicit Sub Hide_Columns() ' Hide_Columns Macro ' December 7, 2005 ' "transpose" from Frank Kabels Hide Rows Macro ' Keyboard Shortcut: NONE ActiveSheet.Unprotect Password:="XYZ" ' Dim RowNdx As Long Dim ColumnNdx As Long ' Dim lastrow As Long Dim LastColumn As Long Application.ScreenUpdating = False ' lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row ' THE FOLLOWING comes up with Runtime error 1004 ' Application defined or object defined error ' I have no idea what this means, other than something is wrong. LastColumn = ActiveSheet.Cells(Columns.Count, "1").End(xlLeft).Column ' For RowNdx = lastrow To 1 Step -1 For ColumnNdx = LastColumn To 1 Step -1 ' If Cells(RowNdx, "B").Value = "x" Then ' Rows(RowNdx).Hidden = True If Cells(ColumnNdx, "1").Value = "x" Then Columns(ColumnNdx).Hidden = True End If ' Next RowNdx Next ColumnNdx Application.ScreenUpdating = True ActiveSheet.Protect Password:="XYZ" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Columns if ..................
Tried it as suggested. Works Great!!!
Thanks so much. "Bob Phillips" wrote: BEEJAY, Try this Sub Hide_Columns() ' Hide_Columns Macro ' December 7, 2005 ' "transpose" from Frank Kabels Hide Rows Macro ' Keyboard Shortcut: NONE ActiveSheet.Unprotect Password:="XYZ" Dim ColumnNdx As Long Dim LastColumn As Long Application.ScreenUpdating = False LastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column For ColumnNdx = LastColumn To 1 Step -1 If Cells(1, ColumnNdx).Value = "x" Then Columns(ColumnNdx).Hidden = True End If Next ColumnNdx Application.ScreenUpdating = True ActiveSheet.Protect Password:="XYZ" End Sub -- HTH RP (remove nothere from the email address if mailing direct) "BEEJAY" wrote in message ... I have been using a hide rows macro from Frank Kabel succesfully for a long time. I now have use for this macro as Hide Columns (instead of rows) I used Franks macro and tried to replace row references to columns but end up with Run time error on the most important line. Can someone help? Option Explicit Sub Hide_Columns() ' Hide_Columns Macro ' December 7, 2005 ' "transpose" from Frank Kabels Hide Rows Macro ' Keyboard Shortcut: NONE ActiveSheet.Unprotect Password:="XYZ" ' Dim RowNdx As Long Dim ColumnNdx As Long ' Dim lastrow As Long Dim LastColumn As Long Application.ScreenUpdating = False ' lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row ' THE FOLLOWING comes up with Runtime error 1004 ' Application defined or object defined error ' I have no idea what this means, other than something is wrong. LastColumn = ActiveSheet.Cells(Columns.Count, "1").End(xlLeft).Column ' For RowNdx = lastrow To 1 Step -1 For ColumnNdx = LastColumn To 1 Step -1 ' If Cells(RowNdx, "B").Value = "x" Then ' Rows(RowNdx).Hidden = True If Cells(ColumnNdx, "1").Value = "x" Then Columns(ColumnNdx).Hidden = True End If ' Next RowNdx Next ColumnNdx Application.ScreenUpdating = True ActiveSheet.Protect Password:="XYZ" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want to Hide columns in spreadsheet but NOT hide data in chart. | Charts and Charting in Excel | |||
Hide/Unhide columns using button on top over relevant columns | Excel Discussion (Misc queries) | |||
Hide columns | Excel Discussion (Misc queries) | |||
Excel button :: Filter columns by value - possible? Additionally, hide certain columns | Excel Programming | |||
Hide Columns | Excel Programming |