I need to hide a range of cells using a macro in excel. C11 contains the column index from where I need to start hiding the columns.
Sub test()
Dim i As Integer
Dim j As Integer
Dim rocket As Range
i = Range("c11").Value
j = 12
rocket = Range(Cells(5, i), Cells(5, j))
Range("Rocket").Select
Selection.EntireColumn.Hidden = True
End Sub
The code is giving some unexpected error and as I am a novice, so have no clue what needs to be done..
Tree steps to make your code working:
1st. Add Set key word in appropriate line which is necessary:
Set rocket = Range(Cells(5, i), Cells(5, j))
2nd. Rocket variable represents range, you will NOT need to call it in this way:
Range("Rocket")....
but
rocket....
3rd. Avoid Select method and Selection object always when possible. Therefore the last two lines replace with this single one (which implements 2nd step, too):
rocket.EntireColumn.Hidden = true
That last answer was awesome! Just for someone else's FYI, here is what worked in Excel 2007. The first line is always 3, but the ending line needed to be a variable. That's where I had the problem. THIS FIXED IT! The last 4 lines before the "End If" do the work. Hope this helps!
Dim RowsToHide As Range
Dim RowHideNum As Integer
' Set Correct Start Dates for Billing in New File
Workbooks("----- Combined_New_Students_Updated.xlsx").Activate
Sheets("2015").Activate
StartDateLine1 = Format(START_DATE_1, "ww") - 1 ' Convert Start Date to Week Number
StartDateLine1 = (StartDateLine1 * 6) - 2 ' Convert Start Date to Line Number
If StartDateLine1 >= "10" Then
Cells(4, "q").Value = ""
Cells(StartDateLine1, "q").Value = STATUS_1
Cells(StartDateLine1, "z").Value = "START DATE " + START_DATE_1
RowHideNum = StartDateLine1 - 2
Set RowsToHide = Range(Cells(3, "a"), Cells(RowHideNum, "ab"))
RowsToHide.Select
RowsToHide.EntireRow.Hidden = True
End If
