Hide Columns if ..................

B

BEEJAY

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
 
B

Bob Phillips

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top