VBA to hide columns

[Guest]

I have a spreadsheet that is structured as below:

July July July August August August September September
September....etc

each of the above are in different columns.

I need to create a macro that will hide those columns that are not within a
start Month (which I have in a named range), lets say it is JULY, and an end
month (which I have in a named range), lets say it is AUGUST.

I have used the below code, however it does not work exactly. What it does
is it shows all the July Columns (great) BUT it only shows the first August
column (whereas I want to see all the August columns.

Sub ReportColumn()
' Commence hidding unselected columns
I = 1
For Each Cell In Worksheets("Report").Range("B3:AK3")
Select Case I
Case 1
If Cell.Value <> Worksheets("Settings").Range("PeriodFrom")
Then
Cell.EntireColumn.Hidden = True
Else
I = I + 3
Cell.EntireColumn.Hidden = False
End If
Case 2
If Cell.Value = Worksheets("Settings").Range("PeriodTo") Then
I = I + 3
Cell.EntireColumn.Hidden = False
End If
Cell.EntireColumn.Hidden = False
Case 3
Cell.EntireColumn.Hidden = True
End Select
Next
End Sub


Any suggestions?????

 

[Norman Jones]

Hi James,

Try:
'==================>>
Public Sub ReportColumn()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim firstCell As Range
Dim lastCell As Range
Dim sStr1 As String, sStr2 As String

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = Worksheets("Report")
Set rng = SH.Range("B3:AK3")

sStr1 = SH.Range("PeriodFrom").Value
sStr2 = SH.Range("PeriodTo").Value

Application.ScreenUpdating = False

Set firstCell = rng.Find(What:=sStr1, _
After:=rng(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set lastCell = rng.Find(What:=sStr2, _
After:=rng(rng.Cells.Count), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

rng.EntireColumn.Hidden = True
Range(firstCell, lastCell).EntireColumn.Hidden = False

Application.ScreenUpdating = True
End Sub
'<<==================

 

[Guest]

I have copied your code and tried to use.

I must admit I am new to this (sure you hear that all the time).

1) I was not sure what to "change" the "set Wb = ActiveWorkbook" to.

2) I tried running the macro given 1 and I get an error message "Runtime
error 1004, Method 'Range' of object '_Worksheet' failed". When I debug it
has highlighted the section " sStr1 = SH.Range("PeriodFrom").Value"

Can you help?

 

[Norman Jones]

Hi James,

1) I was not sure what to "change" the "set Wb = ActiveWorkbook"
to.

If the code is to operate exclusoively on the active workbook, no change is
required. Otherwise, simply specify the workbook name. If, for example, your
workbook is named James.xls, then you might change the assignment statement
to:

Set WB = Workbooks("James.xls")

2) I tried running the macro given 1 and I get an error message
"Runtime error 1004, Method 'Range' of object '_Worksheet'
failed". When I debug it has highlighted the section "
sStr1 = SH.Range("PeriodFrom").Value"

If there is not a range naned 'PeriodFrom' on the worksheet, you will get
the above error. Similarly, if the active workbook does not have a sheet
named 'Report', the code will throw a runtime error.

I tested the code with a worksheet named Report which had two named cells:
PeriodFrom and PeriodTo and the code ran without problem.