Selecting a Variable Column Range

[nuttyrat]

Good Day,

I'll do my best to explain the issue, but please forgive me as i am a VBA newbie with some inherited code soo please be gentle : D . I have a workbook that generates sheets for each year based on selected criteria. It starts at Column H and goes too AH and beyond. When my loop reaches Z it errors out. I think this is happening because the code is referencing the column as ASCII. Here is the code:

Sub Test() 
    Dim d As Date 
    Dim yrint, i, num_years, fields, field_start As Integer 
    Dim yrstr, crit1, crit2, left_column_range_fixed, right_column_range_fixed, left_column_range_var, right_column_range_var, left_column_range, right_column_range, cost_column, cost_column_var, cost_column_fixed As String 
    left_column_range_fixed = "H" 
    right_column_range_fixed = ":AH" 
    cost_column_fixed = "2" 
    crit1 = "=x" 
    crit2 = ">0" 
    d = Date 
    yrint = Sheets("Overall").Range("H2") 
    field_start = 9 'changed from 9
    num_years = InputBox("Enter the number of years") 'inputs the # of years that the macro will create sheets for
    For i = 0 To num_years 'loop begins
        yrstr = CStr(yrint + i) 'changes the year to i + the year in a string
        fields = field_start + i 'sets which rows will be filtered
        left_column_range_var = Chr(Asc("h") + i) 'sets the variable column range for the leftmost columns
        right_column_range_var = Chr(Asc("j") + i) 'sets the variable column range for the rightmost columns
        cost_column_var = Chr(Asc("i") + i) 'sets the variable column range for the cost column
        Sheets("Overall").Select ' this and the next 4 lines create the next years' sheet
        Sheets("Overall").Copy After:=Sheets(i + 1) 
        Sheets("Overall (2)").Select 
        Sheets("Overall (2)").Name = yrstr 'sets the name to the appropriate year
        Selection.AutoFilter Field:=fields, Criteria1:=crit1, Operator:=xlOr, Criteria2:=crit2 'selects the rows to be shown based on whether they have an x or int value in them
        Range("A1:B1").Select 'this and the next line set the sheet title to the correct year
        ActiveCell.FormulaR1C1 = "Tasks to be Completed - " + yrstr 
        Columns("D:E").Select 'hides the task type, time frame, and priority columns
        Selection.EntireColumn.Hidden = True 
        left_column_range = left_column_range_fixed + left_column_range_var 'sets which columns will be hidden
        Columns(left_column_range).Select ' this selects the leftmost column(s) to be hidden
        Selection.EntireColumn.Hidden = True 
        right_column_range = right_column_range_var + right_column_range_fixed 'sets which columns will be hidden
        Columns(right_column_range).Select 'this selects the rightmost column(s) to be hidden
        Selection.EntireColumn.Hidden = True 
        cost_column = cost_column_var + cost_column_fixed + ":" + cost_column_var + cost_column_fixed 'selects the correct column to put the cost formula
        Range(cost_column).Select 
        ActiveCell.FormulaR1C1 = "Cost" 'copies the formula to the correct cell, and names it "Cost"
        ActiveWindow.View = xlPageBreakPreview 'sets the page preview view for the sheet
    Next i 'loop ends
    Worksheets.FillAcrossSheets (Worksheets("Overall").Range("A2:B99")) 'a workaround for the 255 character limit of sheets(x).copy.  Its ugly but it works :-/
    Sheets("Overall").Select 'sets the selected worksheet back to Overall
End Sub 

Thanks in advance for your help

Terry

[Batman]

Hi Terry,

Welcome to the OzGrid forum.

I haven't tried to figure out exactly what your code is trying to do, but perhaps some code along these lines might help, assuming that the variables are numeric:

Range(Cells(1, left_column_range), Cells(1, right_column_range)).EntireColumn.Hidden = True 

Hope this helps.

[nuttyrat]

Sorry i'll try to explain in more detail

Basically what happens is that each row item contains detail data that is to be used only on specific years. What happens is that from Columns H too AH and beyond are each labled as a Year (H2 being 2006, I2 being 2007 , etc). A user will place an X on the specific cell where the row is required for that particular year. What hte macro does is go over each year (starting with H) and autofilters based on cells that have "x" in them. It copies the autofiltered rows into a new sheet (named for the year) and then hides the columns that do not have anything to do with that year. The issue in question has to do with the following:

This code selects the columns which vary for each loop (taken from the code in the original post)

left_column_range_var = Chr(Asc("h") + i) 'sets the variable column range for the leftmost columns
right_column_range_var = Chr(Asc("j") + i) 'sets the variable column range for the rightmost columns

And then it hides the columns selected

left_column_range = left_column_range_fixed + left_column_range_var 'sets which columns will be hidden
Columns(left_column_range).Select ' this selects the leftmost column(s) to be hidden
Selection.EntireColumn.Hidden = True 
right_column_range = right_column_range_var + right_column_range_fixed 'sets which columns will be hidden
Columns(right_column_range).Select 'this selects the rightmost column(s) to be hidden
Selection.EntireColumn.Hidden = True 

When i run this macro for 30 years (30 loops) it gets an error when it tries to hide columns too the right of Z, getting a Run-time error '13', Type mismatch. I'm assuming this is because it is using ASCII too identify which columns to hide ... but i'm not sure because i'm a total newbie at this.

I'll try your suggestion though ... just thought i'd explain it in a little more detail

Thanks

Terry

[nuttyrat]

Bump

[Batman]

Terry,

The reason your macro is erroring out after column Z is that the character number 1 greater than Z is not AA. As I alluded to earlier, try using column numbers rather than letters. Also, I would suggest avoiding selecting objects in order to take action using the Selection object. You can do the same thing, only better, using the original object. E.g.

Columns("D").Select 
Selection.EntireColumn.Hidden = True 

can be replaced by

Columns("D").Hidden = True 
 'or
Columns(4).Hidden = True 

Using numbers for columns you will not have the problem of erroring when you go past Z.

However, to refer to a range of columns by number to hide them you need to use the code I gave you earlier, i.e.

Range(Cells(1, left_column_range), Cells(1, right_column_range)).EntireColumn.Hidden = True 

but this assumes that the variables left_column_range and right-column_range are numeric and not strings.

You should be aware that your coding of the declarations only declares the last item in each declaration statement as the variable type you define. You need to type 'As Integer' (or whatever) after each variable. Otherwise the variable is declared as the default type of Variant.

Hope this helps.