Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to hide columns
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????? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to hide columns
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 '<<================== --- Regards, Norman "James T" wrote in message ... 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????? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to hide columns
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" wrote: 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 '<<================== --- Regards, Norman "James T" wrote in message ... 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????? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to hide columns
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. --- Regards, Norman "James T" wrote in message ... 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" wrote: 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 '<<================== --- Regards, Norman "James T" wrote in message ... 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????? |
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 COLUMNS | Excel Discussion (Misc queries) | |||
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 |