Column Hidding Script not working

[Guest]

Afternoon Guys,

I'm trying to run a script that will hide selected columns. These columns
are selected via spreadsheet in excel. When you click on a particular cell,
it displays a column letter, eg, click "A3" as Yes, --> select column "D"
(results located in C1), select team 'Show Some' (located in G2). Once these
have been selected, the program will then select sheet 'Scorecard' and hide
column D. Please see below. It works fine when it isn't selecting a sheet
but as soon as a sheet select is placed in the script...i hides everything in
that sheet. it should hide selected columns not everything. Please help if
you can. Cheers.

Sub TestingColumn2()
Dim Columns, Hide As String

Hide = Range("H3")


If Range("G2") = Range("G2") Then
Sheets("Administration").Select
Columns = Range("C1")
Sheets("Scorecard").Select
Range("D:AS").Select
Range("A45") = ""
Selection.EntireColumn.Hidden = False
Range(Columns).Activate
Selection.EntireColumn.Hidden = Hide
Range("A45").Value = "Working"
Else
Range("A46") = "Not Working"
End If

End Sub

 

[Johz]

change Range(Columns).Activate into Range(Columns).Select
also, you don't need to select the ranges before you do things to them, that just uses more memory and slows the code:

Hide = Range("H3")


If Range("G2") = Range("G2") Then
Sheets("Administration").Select
Columns = Range("C1")
Sheets("Scorecard").Select
Range("A45") = ""
Range("D:AS").EntireColumn.Hidden = False
Range(Columns).EntireColumn.Hidden = Hide
Range("A45").Value = "Working"
Else
Range("A46") = "Not Working"
End If

 

[okrob]

Afternoon Guys,

I'm trying to run a script that will hide selected columns. These columns
are selected via spreadsheet in excel. When you click on a particular cell,
it displays a column letter, eg, click "A3" as Yes, --> select column "D"
(results located in C1), select team 'Show Some' (located in G2). Once these
have been selected, the program will then select sheet 'Scorecard' and hide
column D. Please see below. It works fine when it isn't selecting a sheet
but as soon as a sheet select is placed in the script...i hides everything in
that sheet. it should hide selected columns not everything. Please help if
you can. Cheers.

Sub TestingColumn2()
Dim Columns, Hide As String

Hide = Range("H3")

If Range("G2") = Range("G2") Then
Sheets("Administration").Select
Columns = Range("C1")
Sheets("Scorecard").Select
Range("D:AS").Select
Range("A45") = ""
Selection.EntireColumn.Hidden = False
Range(Columns).Activate
Selection.EntireColumn.Hidden = Hide
Range("A45").Value = "Working"
Else
Range("A46") = "Not Working"
End If

End Sub

Without seeing your sheet, it's difficult to see where you're going.
I've made some assumptions, so here's a possible solution.
First, you don't need all the selects unless you just want them. You
can do most of this without actually selecting anything.

Sub TestingColumn2()
Dim Columns, Hide As String

Hide = Range("H3")
'assumed that this value was either TRUE
'or FALSE based on some criteria (checkbox, radio, etc...)

If Range("G2") = Range("G2") Then
'I don't really understand why you're testing if a cell equals
itself...
'As it always will be true, why have the if statement?
'Are the ranges supposed to be on different sheets? If so, you need
to state that.

Columns = Sheets("Administration").Range("C1").Value
' assumed that you meant the actual string value for variable
'Columns was the value in the cell C1 on the Admin sheet.

Sheets("Scorecard").Range("A45") = ""
Sheets("Scorecard").Range("D:AS").EntireColumn.Hidden = False
'Did you mean to hide 42 columns here?

Range(Columns).EntireColumn.Hidden = Hide
Range("A45").Value = "Working"
Else
Range("A46") = "Not Working"
End If

End Sub

 

[Guest]

Mornig okrob,

Unfortunately it isn't exactly what i wanted,

For example; in cell G2 (administration sheet), there is a team select.
depending on the team selected in G2, it will hide certain columns in
'scorecard' sheet.

eg. if cell G2 is 'DC Team' then select columns B:B,E:E,G:K and hide those
columns.

each team has different criteria in hiding selected columns in the scorecard
sheet.


i've use a script that has all the criteria but it was too long, that's why
i made this script, it was working at one stage, but not anymore. if you can,
please help