Variable Column range Hidden

[JaccoVis90]

Hey all,

I have a problem getting a range which is variable to hide
Can you help me

Error 1004, methode Range of object_worksheet failed, ( I THINK, i have the error in dutch)
"fout 1004 vba methode Range van object_worksheet is mislukt"

Sub test()


Dim startws, invoer As Worksheet
Dim Af2L1L As Integer
Dim Hschuif, Kolstart, Kolend As String
Dim wsb2 As Worksheet
Dim ws As Worksheet
Dim HVVShape, HKooi as String

Set startwb = ThisWorkbook
Set invoer = startwb.Sheets("Input")

Af2L1L = invoer.Range("C1")     'Value in cm
HVVShape = invoer.Range("D5")
HKooi = invoer.Range("F5")
Hschuif = Af2L1L / 5            'Copy shift(per 5 cm)) (BREEDTE, HORIZONTAAL)
Kolstart = 176 - Hschuif        'From were I need to calculate stuff
Kolend = 176 + Hschuif          'To were I need to calculate stuff

naam2 = "VS " & Af2L1L / 100 & "-" & HVVShape / 100 & "-0,9 BV k-" & Hkooi
Set ws = ThisWorkbook.Sheets.Add(After:= _
         ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = naam2
Set wsb2 = startwb.Sheets(naam2)


'HERE IS A LOT OF (UN)IMPORTANT CODING WHICH WORKS (UNIMPORTANT TO THE PROBLEM I HOPE)
'IT COPIES IN A SHEET WITH VALUES WITH DIFFERENT SHIFTED AREAS
'SO I ONLY WANT TO HAVE THE CELLS WITH MULTIPLE VALUES CALCULATED TOGETHER
'SO I CALCULATE THE MEAN, MAX ETC. BETWEEN Kolstart AND Kolend
'THIS WORKS FINE ONLY THE HIDE COLUMNS NOT




wsb2.Range(Cells(1, 2), Cells(1, (Kolstart - 2))).EntireColumn.Hidden = True
wsb2.Range(Cells(1, Kolend + 2), Cells(1, 351)).EntireColumn.Hidden = True




End Sub

 

[Joe4]

What is the value of "Kolstart" when you get the error?
If you aren't sure, add a message box prior to the line returning the error, i.e.

MsgBox Kolstart

Also, do you have any protected cells or merged cells on your sheet?

 

[Fluff]

Try

With wsb2
   .Range(.Cells(1, 2), .Cells(1, (Kolstart - 2))).EntireColumn.Hidden = True
   .Range(.Cells(1, Kolend + 2), .Cells(1, 351)).EntireColumn.Hidden = True
End With

 

[JaccoVis90]

Kolstart = 100

and on the sheet wsb2 there is no protection and no merged cells

 

[JaccoVis90]

Try

With wsb2
   .Range(.Cells(1, 2), .Cells(1, (Kolstart - 2))).EntireColumn.Hidden = True
   .Range(.Cells(1, Kolend + 2), .Cells(1, 351)).EntireColumn.Hidden = True
End With

Thank you,

This works

The strange thing is that when the rest of the code was less it did work (i tested it and made a note of it that it worked)

What can be the reason of this?

 

[Joe4]

See if Fluff's code resolves your issue.

 

[JaccoVis90]

Thank you Fluff, your code works

Can you give me an explaination why the other didn't work and your did.
So I can learn from it for the future

 

[Fluff]

The part in red

wsb2.Range([COLOR=#ff0000]Cells(1, 2), Cells(1, (Kolstart - 2))[/COLOR]).EntireColumn.Hidden = True

is looking at the activesheet rather than wsb2.
So if wsb2 is the activesheet your code will work, otherwise you will get the error.
What you need to do is qualify the range & the cells so that they are looking at the same sheet. One way to do that is

wsb2.Range(wsb2.Cells(1, 2), wsb2.Cells(1, (Kolstart - 2))).EntireColumn.Hidden = True

Or you can use the shorthand version using the With Statement

 

[JaccoVis90]

Thanks,

that makes sense

 

[Fluff]

You're welcome